Search Existing Toolbars

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
Hi Steve
such as Advanced Filter
Click to expand...

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
 
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.
 
Hi Jay

See my reply that use the ID together with findcontrol.
Working in other languages also then
 
Excellent, Ron. Thanks a ton! Where could I get a list of control ID
numbers (i.e., ID=901) and their descriptions?
 
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.
 
Back
Top