Menubar items

[Andy]

I want to be able to list every active menubar complete with submenus from
VBA but I cannot find a way to do this.
How do I identify the menuitems?

What I am trying to do is to find the location of a heading in the a menu
and find the location of it (ie third heading, dropdown 4)

Andy

 

[Sandra Daigle]

Please see my reply to your post in microsoft.public.commandbars.

Rather than posting the same message to more than one group (multiposting)
please cross-post (send the one message to all groups at once). To
Crosspost, put all of the newsgroups into the Newsgroups field in the
message header. In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times.

See http://www.mvps.org/access/netiquette.htm for more information.

 

[Andy]

Please see my reply to your post in microsoft.public.commandbars.

Rather than posting the same message to more than one group (multiposting)
please cross-post (send the one message to all groups at once). To
Crosspost, put all of the newsgroups into the Newsgroups field in the
message header. In this way, all responses to your post will be available
together, regardless of what group the responder was in, and the rest of us
won't have to read your post multiple times.

See http://www.mvps.org/access/netiquette.htm for more information.

Sorry, I did intend to cross-post but I hit send by mistake
Thanks for the reply BTW but unfortunately I am using access 97, I should
have mentioned that.

Thanks again

Andy

 

[Sandra Daigle]

Hi Andy,

No problem on the multipost - sometimes it happens by accident, more often
it happens when someone is unfamiliar with the newsgroups. Not having a
working crystal ball handy <g>, I generally just go ahead and post the link
and the standard instructions on crossposting vs multiposting.

Yes, the version of Access is relevant in this questioin - only because my
sample is only for A2000 and above. I'll convert it to A97 and get someone
to try it out. If all goes well, I'll put it on my website.

Basically you enumerate the commandbars collection, then with each
commandbar you enumerate the commandbar.controls collection. Here's the
tricky part, for each commandbar control if the type is msoControlPopup you
must also enumerate its controls collection. In my code, this is done
recursively. Here is some cut-down code to get you started. I think it will
work in A97 but it has been so long since I've actually used A97 that I
honestly don't know for sure!

Sub listCommandBars()
Dim cb As CommandBar
Dim lngCBId As Long
For Each cb In CommandBars
'take out this test if you want ALL commandbars
If cb.BuiltIn = False Then
Debug.Print "****************"
Debug.Print "* CommandBar *"
Debug.Print cb.Name, cb.Position
listCBControls cb.Name
Debug.Print "* CommandBar *"
Debug.Print "****************"
endif
Next cb
set cb=nothing
End Sub

Public Sub listCBControls(pstrCBName As String)
Dim cbi As CommandBarControl
Dim lngCBItemid As Long
Dim lngGroup As Long
Dim lngSort As Long
lngGroup = 1
lngSort = 1
For Each cbi In CommandBars(pstrCBName).Controls
Debug.Print cbi.Caption, cbi.Type, cbi.Index, cbi.Id, cbi.Tag
If cbi.Type = msoControlPopup Then
listCBMenuItems cbi
End If
Next cbi
set cbi=nothing
End Sub

Sub listCBMenuItems(pCbiMenu As CommandBarControl)
Dim cbi As CommandBarControl
Dim lngCBItemid As Long
Dim lngGroup As Long
Dim lngSort As Long
lngGroup = 1
lngSort = 1
Debug.Print "****************"
Debug.Print "* Sub Menu *"
For Each cbi In pCbiMenu.Controls
Debug.Print cbi.Caption, cbi.Type, cbi.Index, cbi.Id, cbi.Tag
If cbi.Type = msoControlPopup Then
listCBMenuItems cbi
End If
Next cbi
Debug.Print "* End Sub Menu *"
Debug.Print "****************"
set cbi=nothing
End Sub

 

[Andy]

Hi Andy,
Basically you enumerate the commandbars collection, then with each
commandbar you enumerate the commandbar.controls collection. Here's the
tricky part, for each commandbar control if the type is msoControlPopup you
must also enumerate its controls collection. In my code, this is done
recursively. Here is some cut-down code to get you started. I think it will
work in A97 but it has been so long since I've actually used A97 that I
honestly don't know for sure!

I tried the code but it fails on CommandBarControls is this available via an
add-in?
I maybe able to do this in AccessXP but unfortunately the person I am
writing this for only has Access97 :-(
I appreciate the problem with Access 97, I have used XP for a while now and
found it tough to 'think down' to 97.

Thanks again

Andy

 

[Sandra Daigle]

Hi Andy,

I tried the code but it fails on CommandBarControls is this available
via an add-in?

Should be singular - CommandBarControl. I am guessing that this was just a
typo in your post not in your code.

Is it a compile error on this line:

Dim cbi As CommandBarControl

The only special thing you need is a reference to the Microsoft Office 8.0
Object Library.

 

[Sandra Daigle]

Also, here's an article that is written for Access97:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc97/html/msdn_command.asp

 

[Andy]

Hi Andy,


Should be singular - CommandBarControl. I am guessing that this was just a
typo in your post not in your code.

Is it a compile error on this line:

Dim cbi As CommandBarControl

The only special thing you need is a reference to the Microsoft Office 8.0
Object Library.

Exactly what I needed. Many thanks.
I was tearing my hair (what little remains out because I could not
understand why I couldn't reference the command bars in the way that access
references everything else. The ref to MSOffice makes it all work.

Thanks again

Andy

 

[Sandra Daigle]

Good deal - sorry I didn't suggest checking the reference sooner!