Tip Printed from ExcelTip.com
Change the availability for a menu item using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions



The macro below shows how you can toggle the availability state for a menu item. 
Sub ToggleMenuControls()
Dim m As CommandBarControl, mi As CommandBarControl
    Set m = CommandBars.FindControl(ID:=30002) ' File Menu
    If m Is Nothing Then Exit Sub
    For Each mi In m.Controls
        If mi.ID = 18 Then mi.Enabled = Not mi.Enabled 
        ' toggles the state for the Print menu
    Next mi
    Set mi = Nothing
    Set m = Nothing
End Sub