By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,893 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

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

P: 2
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!
Nov 19 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 5K+
P: 8,623
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.
    Expand|Select|Wrap|Line Numbers
    1. Dim cbp As CommandBarPopup
    2. Dim varWeekDays As Variant
    3. Dim intCtr As Integer
    5. 'Create an Array of Weekdays
    6. varWeekDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday")
    8. 'Create the Top Level Menu
    9. Set cbp = CommandBars("Menu Bar").Controls.Add(msoControlPopup, Temporary:=True)
    10. cbp.Caption = "&Weekdays"
    12. With cbp.CommandBar.Controls
    13.   For intCtr = LBound(varWeekDays) To UBound(varWeekDays)
    14.     With .Add(msoControlButton)
    15.       .Caption = varWeekDays(intCtr)
    16.       .OnAction = "fProcessWeekdays"
    17.     End With
    18.   Next intCtr
    19. 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:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fProcessWeekdays()
    2. Dim strCaption As String
    3. Dim cbc As CommandBarControl
    4. Dim cbcItem As CommandBarControl
    6. 'Store the Caption of the Active Control
    7. Set cbc = CommandBars.ActionControl
    8. strCaption = cbc.Caption
    10. 'Loop through all Controls in the CommandBar Object that is
    11. 'the Parent of the Selected Control
    12. For Each cbcItem In cbc.Parent.Controls
    13.   With cbcItem
    14.     'Check the Selected Item, Uncheck all the rest
    15.     If .Caption = cbc.Caption Then
    16.       cbcItem.State = msoButtonDown
    17.     Else
    18.       cbcItem.State = msoButtonUp
    19.     End If
    20.   End With
    21. Next
    22. End Function
  4. To arbitrarilly Check/Uncheck specific Menu Options in the Weekdays Menu (long...... syntax):
    Expand|Select|Wrap|Line Numbers
    1. 'Check Monday, Thursday, and Saturday
    2. CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Monday").State = msoButtonDown
    3. CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Thursday").State = msoButtonDown
    4. CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Saturday").State = msoButtonDown
    6. 'Uncheck Monday, Thursday, and Saturday
    7. 'CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Monday").State = msoButtonUp
    8. 'CommandBars("Menu Bar").Controls("&Weekdays").CommandBar.Controls("Thursday").State = msoButtonUp
    9. '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.
Attached Files
File Type: zip (17.4 KB, 156 views)
Nov 20 '10 #2

P: 2
Awesome! This is exactly what I was looking for.
Nov 22 '10 #3

Expert 5K+
P: 8,623
You are quite welcome.
Nov 22 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.