Ultimate VBA code for commandbar control

[Kelvin Stott]

Hello to all,

I need a very short and simple piece of code that disables all commandbar controls based on a pre-defined list of ID numbers. I imagine it would have the following basic structure:

1. Define a list of commandbar control IDs (19, 21, 22, etc.)
2. Disable each commandbar control with ID in pre-defined list

Maybe something like this:

CBCList = (19, 21, 22, etc.)
For Each CB in CBCList
       Commandbar(CB).Enabled = False
Next

Of course this "code" will not work, however I don't know enough VBA to write something that works.

Could somebody please help me with this, as I imagine this would be very useful to lots of other people also.

Many thanks,
Kelvin

 

[JamesW]

Hi there,

You are nearly there..

Sub test()
    CBCList = Array(17, 16, 15, 19, 21, 22, 24)
    For Each CB In CBCList
        Application.CommandBars(CB).Enabled = False
    Next
End Sub

 

[Kelvin Stott]

Many thanks!

Your code didn't quite work as the numbers in the list/array weren't recognized as CommandBarControl ID numbers, however I was able to play around with it and the following works like a charm:

    CBCList = Array(19, 21, 22)
    Dim CBC As CommandBarControl
    
    On Error Resume Next
    For Each CBCID In CBCList
    For Each CBC In CommandBars.FindControls(ID:=CBCID)
        CBC.Enabled = False
    Next
    Next

Essentially you can use this code to disable all commandbar controls with ID numbers defined in the array, so just what I was looking for!

Thanks again,
Kelvin

 

[JamesW]

Yeah I just added random numbers in there Glad you got it to work.

 

[tomcatonnet99]

Is there a list of control ID numbers that one can refer to ?

for excel as well as VB commandbars... (Especially: Remove sheet in the File menu of VB Editor... I seem to have lost that... )

further, what exactly is the correct coding for something like


Application.Commandbars("File").Controls("Print").enabled =false
Doesn't work for me in Excel 2003... even if I try

commandbars("Worksheet Menu Bar").controls("&File").Controls("&Print").enabled = false

cheers

Tom