Search Existing Toolbars
 

When a workbook is opened, is there any way to search all available toolbars
for the existence of a particular button (i.e., customized buttons a user may
have added in another workbook, such as Advanced Filter...) and if found,
disable it? Thanks.
--
Steve C

Search Existing Toolbars
 

Hi Steve

such as Advanced Filter

http://www.rondebruin.nl/menuid.htm

Sub MenuControl_False()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=901)
Ctrl.Enabled = False
Next Ctrl
End Sub

Sub MenuControl_True()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=901)
Ctrl.Enabled = True
Next Ctrl
End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steve C" wrote in message ...
When a workbook is opened, is there any way to search all available toolbars
for the existence of a particular button (i.e., customized buttons a user may
have added in another workbook, such as Advanced Filter...) and if found,
disable it? Thanks.
--
Steve C

Search Existing Toolbars
 

Hi Steve -

Sub SteveC()
For Each cmdBar In Application.CommandBars
For Each ctrl cmdBar.Controls
If ctrl.Caption = "&Advanced Filter..." Then 'Adjust name to suite*
With ctrl
.Visible = True 'True or False to suit
.Enabled = False 'True or False to suit
End With
End If
Next 'ctrl
Next 'cmdBar
End Sub

*Footnote: To find the exact name of the control (button) you're interested
in, rightclick in any toolbar, select 'Customize...' and then rightclick the
button to see its Name property.

Reminder: Any change you make with the above code is completely reversible,
but is 'permanent' until explicitly reversed by the above code (change false
to true or vice versa) or by some other existing code. The modified toolbar
is saved when excel closes.
--
Jay


"Steve C" wrote:

When a workbook is opened, is there any way to search all available toolbars
for the existence of a particular button (i.e., customized buttons a user may
have added in another workbook, such as Advanced Filter...) and if found,
disable it? Thanks.
--
Steve C

Search Existing Toolbars
 

Hi Jay

See my reply that use the ID together with findcontrol.
Working in other languages also then

--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jay" wrote in message ...
Hi Steve -

Sub SteveC()
For Each cmdBar In Application.CommandBars
For Each ctrl cmdBar.Controls
If ctrl.Caption = "&Advanced Filter..." Then 'Adjust name to suite*
With ctrl
.Visible = True 'True or False to suit
.Enabled = False 'True or False to suit
End With
End If
Next 'ctrl
Next 'cmdBar
End Sub

*Footnote: To find the exact name of the control (button) you're interested
in, rightclick in any toolbar, select 'Customize...' and then rightclick the
button to see its Name property.

Reminder: Any change you make with the above code is completely reversible,
but is 'permanent' until explicitly reversed by the above code (change false
to true or vice versa) or by some other existing code. The modified toolbar
is saved when excel closes.
--
Jay


"Steve C" wrote:

When a workbook is opened, is there any way to search all available toolbars
for the existence of a particular button (i.e., customized buttons a user may
have added in another workbook, such as Advanced Filter...) and if found,
disable it? Thanks.
--
Steve C

Search Existing Toolbars
 

Excellent, Ron. Thanks a ton! Where could I get a list of control ID
numbers (i.e., ID=901) and their descriptions?
--
Steve C


"Ron de Bruin" wrote:

Hi Steve

such as Advanced Filter

http://www.rondebruin.nl/menuid.htm

Sub MenuControl_False()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=901)
Ctrl.Enabled = False
Next Ctrl
End Sub

Sub MenuControl_True()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=901)
Ctrl.Enabled = True
Next Ctrl
End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steve C" wrote in message ...
When a workbook is opened, is there any way to search all available toolbars
for the existence of a particular button (i.e., customized buttons a user may
have added in another workbook, such as Advanced Filter...) and if found,
disable it? Thanks.
--
Steve C

Search Existing Toolbars
 

Hi Steve

See my site and download Ole's add-in (link is on the site)
http://www.rondebruin.nl/menuid.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steve C" wrote in message ...
Excellent, Ron. Thanks a ton! Where could I get a list of control ID
numbers (i.e., ID=901) and their descriptions?
--
Steve C


"Ron de Bruin" wrote:

Hi Steve

such as Advanced Filter

http://www.rondebruin.nl/menuid.htm

Sub MenuControl_False()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=901)
Ctrl.Enabled = False
Next Ctrl
End Sub

Sub MenuControl_True()
' Excel 2000 - 2003
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=901)
Ctrl.Enabled = True
Next Ctrl
End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Steve C" wrote in message ...
When a workbook is opened, is there any way to search all available toolbars
for the existence of a particular button (i.e., customized buttons a user may
have added in another workbook, such as Advanced Filter...) and if found,
disable it? Thanks.
--
Steve C

Search Existing Toolbars
 

Thanks for your input, Jay. It is appreciated!
--
Steve C


"Jay" wrote:

Hi Steve -

Sub SteveC()
For Each cmdBar In Application.CommandBars
For Each ctrl cmdBar.Controls
If ctrl.Caption = "&Advanced Filter..." Then 'Adjust name to suite*
With ctrl
.Visible = True 'True or False to suit
.Enabled = False 'True or False to suit
End With
End If
Next 'ctrl
Next 'cmdBar
End Sub

*Footnote: To find the exact name of the control (button) you're interested
in, rightclick in any toolbar, select 'Customize...' and then rightclick the
button to see its Name property.

Reminder: Any change you make with the above code is completely reversible,
but is 'permanent' until explicitly reversed by the above code (change false
to true or vice versa) or by some other existing code. The modified toolbar
is saved when excel closes.
--
Jay


"Steve C" wrote:

When a workbook is opened, is there any way to search all available toolbars
for the existence of a particular button (i.e., customized buttons a user may
have added in another workbook, such as Advanced Filter...) and if found,
disable it? Thanks.
--
Steve C

Search Existing Toolbars
 

Thanks Ron, excellent solution. More universal and robust to use ID's.
Casual users may find it tricky to track down ID's (if they don't know 'bout
your website !), so I figured Steve (and others) might benefit from two
approaches.

Keep up the good work - your website is a treasure.
--
Jay


"Ron de Bruin" wrote:

Hi Jay

See my reply that use the ID together with findcontrol.
Working in other languages also then

--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Jay" wrote in message ...
Hi Steve -

Sub SteveC()
For Each cmdBar In Application.CommandBars
For Each ctrl cmdBar.Controls
If ctrl.Caption = "&Advanced Filter..." Then 'Adjust name to suite*
With ctrl
.Visible = True 'True or False to suit
.Enabled = False 'True or False to suit
End With
End If
Next 'ctrl
Next 'cmdBar
End Sub

*Footnote: To find the exact name of the control (button) you're interested
in, rightclick in any toolbar, select 'Customize...' and then rightclick the
button to see its Name property.

Reminder: Any change you make with the above code is completely reversible,
but is 'permanent' until explicitly reversed by the above code (change false
to true or vice versa) or by some other existing code. The modified toolbar
is saved when excel closes.
--
Jay


"Steve C" wrote:

When a workbook is opened, is there any way to search all available toolbars
for the existence of a particular button (i.e., customized buttons a user may
have added in another workbook, such as Advanced Filter...) and if found,
disable it? Thanks.
--
Steve C