Help with Enabling / Disabling Menu Items..!

[Applewine]

I have created a menu bar using the following VBA (relevant portion
anyway):

' Add a new menu EMPLOYEES
Set NewMenu = NewMenuBar.Controls.Add _
(Type:=msoControlPopup)
NewMenu.Caption = "&Employees"

' Add a new menu item SHOW INFO
Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton)
With NewItem
..Caption = "&Show Info"
..OnAction = "OpenStaffInfo"
End With

' Add a new menu item HIDE INFO
Set NewItem = NewMenu.Controls.Add(Type:=msoControlButton)
With NewItem
..Caption = "&Hide Info"
..OnAction = "HideStaffinfo"
End With

---I have tried everything syntax-wise to disable each menu item if the
other one is selected, putting the code in each sub:

Sub OpenStaffInfo()
Columns("A:C").Select
Selection.EntireColumn.Hidden = False
‘ Disable this menu option code here (nothing seems to work!)
‘ Enable Hide Staff Info menu option here (nothing seems to work!)
End Sub

Sub HideStaffInfo()
Columns("A:C").Select
Selection.EntireColumn.Hidden = True
‘ Disable this menu option code here (nothing seems to work!)
‘ Enable Open Staff Info menu option here (nothing seems to
work!)
End Sub

VBA help and all my books still lead me to syntax errors...I am still a
bit of a novice no formal training

Help would be very appreciated...I am so close this drives me crazy!

-Dave

 

[JE McGimpsey]

One way:

With .Controls.Add(Type:=msoControlPopup)
.Caption = "&Employees"

' Add a new menu item SHOW INFO
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Show Info"
.OnAction = "OpenStaffInfo"
.Tag = "abcShow"
.Enabled = Range("A:C").EntireColumn.Hidden
End With

' Add a new menu item HIDE INFO
With .Controls.Add(Type:=msoControlButton)
.Caption = "&Hide Info"
.OnAction = "HideStaffinfo"
.Tag = "abcHide"
.Enabled = Not Range("A:C").EntireColumn.Hidden
End With
End With


Public Sub OpenStaffInfo()
Columns("A:C").EntireColumn.Hidden = False
CommandBars.FindControl(Tag:="abcShow").Enabled = False
CommandBars.FindControl(Tag:="abcHide").Enabled = True
End Sub

Public Sub HideStaffInfo()
Columns("A:C").EntireColumn.Hidden = True
CommandBars.FindControl(Tag:="abcShow").Enabled = True
CommandBars.FindControl(Tag:="abcHide").Enabled = False
End Sub