disable menu option

[ingrid]

I created a menubar with some option. I was wondering if it is possible to disable (gry out) an option based a on if then formula?

 

[Alex McDevitt]

....

If ..... then
Application.CommandBars("NameOfMenuBar").Controls("NameOfControl").Enabled = False
End If

 

[Oldsoftboss]

A long winded response but does work.
1.. create your MenuBar

2.. set references in the VB window to Microsoft OFFICE 9.0 (or 10) Object Library

3.. copy the following code to a module:

Dim MyBar As Variant

Public Function CommandbarEnable(Cmdbar As CommandBar, CmdbarEnabled As Boolean, TopLevel As Integer, Optional Sublevel As Integer)
Dim SubCommandbar
On Error GoTo Err_CommandBarEnable

'If the command bar is not visible, make it so.
If Cmdbar.Visible = False Then Cmdbar.Visible = True

'If no menu item on a submenu is selected for enabling\disabling,
'enable\disable the top level menu choice only.
If IsMissing(Sublevel) Or Sublevel = 0 Then
Cmdbar.Controls(TopLevel).Enabled = CmdbarEnabled
'If a menu item on a submenu is selected for
'enabling\disabling, do so now.
Else
Set SubCommandbar = Cmdbar.Controls(TopLevel)
SubCommandbar.Controls(Sublevel).Enabled = CmdbarEnabled
End If

Exit_CommandBarEnable:
Exit Function

Err_CommandBarEnable:
MsgBox "Error " & CStr(Err) & " " & Err.Description & _
" has occurred in the CommandBarEnable Function", vbOKOnly, _
"Error Detected"
Resume Exit_CommandBarEnable

End Function

Public Function CheckMenuItem()
'Disables the 'Company Info' command on the menu bar

If "Your Criteria" = True Then
MyBar = CommandbarEnable(CommandBars("ServiceOperations"), True, 3, 4)
Else
MyBar = CommandbarEnable(CommandBars("ServiceOperations"), False, 3, 4)
End If

End Function

Change the name from "ServiceOperations" to the name of your custom menu bar name.
This enables / disables the 4th item down in the 3rd menu item across

4.. Call CheckMenuItem from your form

Hope you can follow
Dave