"Grey out" menu

[tiso]

I'm trying to "grey out" most of the menus. I'm using the code:

---------------------------------------------
Private Sub Workbook_Open()

Application.CommandBars("Edit").Enabled = False
Application.CommandBars("View").Enabled = False
Application.CommandBars("Insert").Enabled = False
Application.CommandBars("Format").Enabled = False
Application.CommandBars("Tools").Enabled = False
Application.CommandBars("Data").Enabled = False
Application.CommandBars("Window").Enabled = False

End Sub

----------------------------------------------

There may be a better way to do this, but the above code works - with one exception: The menu "Insert" will still work. How come?

[TonyJollans]

Hi tiso,

You are disabling the wrong thing. The "Insert" control on the Menu does not bring up the "Insert" toolbar. Try, instead,[vba]commandbars("Worksheet menu bar").Controls ("Insert").enabled = false[/vba]

[tiso]

Sorry, that doesn't work either. Maybe I'm not explaining my problem in an understandable way: My code works fine with all the menus "Edit", "Tools", "Format" and so on - they are "greyed out". But not "Insert". I want the user only to be able to choose the menus under "File".

Maybe "Insert" is not the correct term in english for the menu between "View" and "Format" (I'm using a non-english version)?

[TonyJollans]

I think I understand, and "Insert" is the correct English term - but maybe "Worksheet Menu Bar" in my example is not the correct Swedish term - did you translate it? I believe CommandBars(1) should work in all language versions. Alternatively, are you perhaps in a Chart Sheet in which case you will need to use CommandBars("Chart Menu Bar") (or the Swedish equivalent) or CommandBars(2).

If that still doesn't help, try[vba]CommandBars.FindControl(ID:=30005).Enabled = True[/vba]

[tiso]

CommandBars is working in my version, since I'm using Application.CommandBars("the menu I want greyed out").Enabled = False, and that works fine with all the main menus, they are "greyed out" when I'm opening the workbook - except the menu "Insert", that's still active.... How come it's working with all the other main menus, but not "Insert"

[TonyJollans]

I'm not at all sure that we are communicating here

You do not want to disable commandbars.
You want to disable controls.

What happens when you disable the controls, for example ... CommandBars(1).Controls(5).Enabled = False

[johnske]

Is this what you're trying to do?
[VBA]Option Explicit
'
Private Sub Workbook_Open()
'
Dim N As Long
'
For N = 1 To Application.CommandBars(1).Controls.Count
'change 1 below ("File") to 4 for the "Insert" control menu
If N <> 1 Then Application.CommandBars(1).Controls(N).Enabled = False
Next
'
End Sub
'
'<< NOTE: Always undo all changes to the UI before closing >>
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
Dim N As Long
'
For N = 1 To Application.CommandBars(1).Controls.Count
Application.CommandBars(1).Controls(N).Enabled = True
Next
'
End Sub[/VBA]

[tiso]

I'm not at all sure that we are communicating here

You do not want to disable commandbars.
You want to disable controls.

What happens when you disable the controls, for example ... CommandBars(1).Controls(5).Enabled = False

Then I get ERROR #9 But if I start with Application.CommandBars(1)... then it works great, thank you (I'm learning some english at the same time here.... )

[tiso]

Is this what you're trying to do?
[vba]Option Explicit
'
Private Sub Workbook_Open()
'
Dim N As Long
'
For N = 1 To Application.CommandBars(1).Controls.Count
'change 1 below ("File") to 4 for the "Insert" control menu
If N <> 1 Then Application.CommandBars(1).Controls(N).Enabled = False
Next
'
End Sub
'
'<< NOTE: Always undo all changes to the UI before closing >>
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
Dim N As Long
'
For N = 1 To Application.CommandBars(1).Controls.Count
Application.CommandBars(1).Controls(N).Enabled = True
Next
'
End Sub[/vba]

Your code is of course SO much better looking than mine. I will try that instead of my ugly one. Thank you!