How to create check boxes in a menu and have them toggle?

[Kylee Lothrop]

Hi, I was wondering if anyone knows how to create check boxes in a menu and have them toggle in VBA (Access 2003). Another option is to use a comboBox but I think this looks bad and a dropdown menu would look better.

I have tried using the .FaceID and selecting a blank for one and a check for the other. In .onAction subroutine, I tried to access the FaceID and just switch the two (so the selected item has the check and the other one blank image) but I can't figure out how to set the control to the menu item and access these faceID's to be able to change them. I would post code but it is dead wrong, and I am sure there is a better way to do this but dont know how. Please help!

[ADezii]

Ironically, you Check/Uncheck Menu Items by manipulating their 'State' Property of the CommandBarControl Object. The following will illustrate this point:

1. Set a Reference to the Microsoft Office XX.X Object Library.
2. Execute the following Code will will create a New Weekdays Menu as the last Menu on your Main Menu. This Drop Down Menu will list the Days of the Week.
   

   Dim cbp As CommandBarPopup
   Dim varWeekDays As Variant
   Dim intCtr As Integer
    
   'Create an Array of Weekdays
   varWeekDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
    
   'Create the Top Level Menu
   Set cbp = CommandBars("Menu Bar").Controls.Add(msoControlPopup, Temporary:=True)
   cbp.Caption = "&Weekdays"
    
   With cbp.CommandBar.Controls
     For intCtr = LBound(varWeekDays) To UBound(varWeekDays)
       With .Add(msoControlButton)
         .Caption = varWeekDays(intCtr)
         .OnAction = "fProcessWeekdays"
       End With
     Next intCtr
   End With

3. Clicking on any given Day will place a Check Mark next to that Day, and clears the Mark for all other Days. This is handled by the OnAction Property of each Menu Option which calls the fProcessWeekdays() Function when a Menu Option is clicked. See Code Reference below:
   

   Public Function fProcessWeekdays()
   Dim strCaption As String
   Dim cbc As CommandBarControl
   Dim cbcItem As CommandBarControl
    
   'Store the Caption of the Active Control
   Set cbc = CommandBars.ActionControl
   strCaption = cbc.Caption
    
   'Loop through all Controls in the CommandBar Object that is
   'the Parent of the Selected Control
   For Each cbcItem In cbc.Parent.Controls
     With cbcItem
       'Check the Selected Item, Uncheck all the rest
       If .Caption = cbc.Caption Then
         cbcItem.State = msoButtonDown
       Else
         cbcItem.State = msoButtonUp
       End If
     End With
   Next
   End Function

4. To arbitrarilly Check/Uncheck specific Menu Options in the Weekdays Menu (long...... syntax):
   

   'Check Monday, Thursday, and Saturday
   CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Monday").State = msoButtonDown
   CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Thursday").State = msoButtonDown
   CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Saturday").State = msoButtonDown
    
   'Uncheck Monday, Thursday, and Saturday
   'CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Monday").State = msoButtonUp
   'CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Thursday").State = msoButtonUp
   'CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Saturday").State = msoButtonUp

5. I do realize that this is an awful lot to absorb, so simply download the Demo Attachment that I have created for you.

[Kylee Lothrop]

Awesome! This is exactly what I was looking for.
Thank-you!

[ADezii]

You are quite welcome.