Generic menu handling code

[DFS]

Anyone written any code to work with the CommandBar object?

I have a custom menubar, with 6 buttons, and up to 3 levels of nested menus,
such as:

File
File | Print
(that's 2 levels)
Projects
Projects | Add
Projects | Add | Type 1
Projects | Add | Type 2
(3 levels)
Reports
(1 level)
I want to iterate the CommandBar collection with a loop, down to the Nth
level, setting all the objects Enabled and Visible. Something like my code
below, but mine bombs when there's no nested menu, like on the Reports item
in my example:
Dim menuObject As Object, mObject As Object, mObject1 As Object

Set menuObject = CommandBars("MainMenu")

For Each mObject In menuObject.Controls
mObject.Visible = True
mObject.Enabled = True

For Each mObject1 In mObject.Controls
mObject1.Enabled = True
mObject1.Visible = True
Next mObject1

Next mObject
Thanks

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I created this when I was investigating the CommandBars collection.

Sub enumCommandBars()
Dim cbr As CommandBar
For Each cbr In Application.CommandBars
Debug.Print ":"; cbr.Name; ":"
Debug.Print "Index: " & cbr.index
Debug.Print "NameLocal: " & cbr.NameLocal
Debug.Print "Type: " & cbr.Type
Dim ctl As CommandBarControl
For Each ctl In cbr.Controls
Debug.Print " "; ":"; ctl.Caption; ":"
Next ctl
If vbCancel = MsgBox(cbr.Name & " done", vbOKCancel) Then
Exit Sub
End If
Debug.Print
Next cbr

End Sub

If you run this in Acc2K, or greater, the MsgBox displays on the db
window side & you can't see the Debug window (PITA). You could
comment it out & the debug window will be filled w/ the results of the
run.

Sub menus are referred to like this:

Dim ctl As CommandBarControl
Set ctl = CommandBars("Menu Bar").Controls(strItem)
ctl.Controls(intIndex).Visible = True

strItem is the main menu item name (like "File" or "Edit").
intIndex is the .Index value of the submenu.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDWwh4echKqOuFEgEQKHRgCeNFJrS1K+NB9U/r4+X3J2HC//H5AAoJZ9
jXPUAWclOOstM/euBJZ/9YVC
=ewX/
-----END PGP SIGNATURE-----

DFS wrote:

Anyone written any code to work with the CommandBar object?

< SNIP >
I want to iterate the CommandBar collection with a loop, down to the Nth
level, setting all the objects Enabled and Visible. Something like my code
below, but mine bombs when there's no nested menu, like on the Reports item
in my example:

< SNIP >

[DFS]

Thanks MG, but I'm using Access97 and can't get that to work.

There's no CommandBar or CommandBarControl object.

"MGFoster" <[e-mail]> wrote in message
news:Dh****************@newsread2.news.pas.earthli nk.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I created this when I was investigating the CommandBars collection.

Sub enumCommandBars()
Dim cbr As CommandBar
For Each cbr In Application.CommandBars
Debug.Print ":"; cbr.Name; ":"
Debug.Print "Index: " & cbr.index
Debug.Print "NameLocal: " & cbr.NameLocal
Debug.Print "Type: " & cbr.Type
Dim ctl As CommandBarControl
For Each ctl In cbr.Controls
Debug.Print " "; ":"; ctl.Caption; ":"
Next ctl
If vbCancel = MsgBox(cbr.Name & " done", vbOKCancel) Then
Exit Sub
End If
Debug.Print
Next cbr

End Sub

If you run this in Acc2K, or greater, the MsgBox displays on the db
window side & you can't see the Debug window (PITA). You could
comment it out & the debug window will be filled w/ the results of the
run.

Sub menus are referred to like this:

Dim ctl As CommandBarControl
Set ctl = CommandBars("Menu Bar").Controls(strItem)
ctl.Controls(intIndex).Visible = True

strItem is the main menu item name (like "File" or "Edit").
intIndex is the .Index value of the submenu.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDWwh4echKqOuFEgEQKHRgCeNFJrS1K+NB9U/r4+X3J2HC//H5AAoJZ9
jXPUAWclOOstM/euBJZ/9YVC
=ewX/
-----END PGP SIGNATURE-----

DFS wrote:

Anyone written any code to work with the CommandBar object?

< SNIP >

I want to iterate the CommandBar collection with a loop, down to the Nth
level, setting all the objects Enabled and Visible. Something like my code below, but mine bombs when there's no nested menu, like on the Reports item in my example:

< SNIP >

[Salad]

There is a discussion on command bars in the A97 Developers Handbook if you have
that available.

DFS wrote:

Anyone written any code to work with the CommandBar object?

I have a custom menubar, with 6 buttons, and up to 3 levels of nested menus,
such as:

File
File | Print
(that's 2 levels)

Projects
Projects | Add
Projects | Add | Type 1
Projects | Add | Type 2
(3 levels)

Reports
(1 level)

I want to iterate the CommandBar collection with a loop, down to the Nth
level, setting all the objects Enabled and Visible. Something like my code
below, but mine bombs when there's no nested menu, like on the Reports item
in my example:

Dim menuObject As Object, mObject As Object, mObject1 As Object

Set menuObject = CommandBars("MainMenu")

For Each mObject In menuObject.Controls
mObject.Visible = True
mObject.Enabled = True

For Each mObject1 In mObject.Controls
mObject1.Enabled = True
mObject1.Visible = True
Next mObject1

Next mObject

Thanks

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I developed that code in Acc97. Make sure the Reference (VBA window
menu Tool > References) "The Microsoft Office # Object library" is
checked. # = version of Office. I believe Office 97 version is 8.0.
The Office library has the CommandBar controls.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDkeEYechKqOuFEgEQLJIQCfe9kRQ3lW8DEfILzhALQu27 UNcY8An3fD
bJ6BMWerxBqRVmK87aKtcYHu
=8F4M
-----END PGP SIGNATURE-----
DFS wrote:

Thanks MG, but I'm using Access97 and can't get that to work.

There's no CommandBar or CommandBarControl object.

[DFS]

"MGFoster" <[e-mail]> wrote in message
news:e6*****************@newsread2.news.pas.earthl ink.net...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I developed that code in Acc97. Make sure the Reference (VBA window
menu Tool > References) "The Microsoft Office # Object library" is
checked. # = version of Office. I believe Office 97 version is 8.0.
The Office library has the CommandBar controls.

I'm using Access97. Microsoft Access 8.0 Object Library is referenced.

The lines "Dim cbr As CommandBar" and "Dim ctl As CommandBarControl" each
throw "User-defined type not defined". But if I change them to "Dim cbr as
Variant" and "Dim ctl As Variant" it works.

But it still doesn't iterate through all the nested submenus of my custom
menu, and give me their properties.
Thanks

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDkeEYechKqOuFEgEQLJIQCfe9kRQ3lW8DEfILzhALQu27 UNcY8An3fD
bJ6BMWerxBqRVmK87aKtcYHu
=8F4M
-----END PGP SIGNATURE-----
DFS wrote:

Thanks MG, but I'm using Access97 and can't get that to work.

There's no CommandBar or CommandBarControl object.

[Matthew Sullivan]

On Mon, 23 Feb 2004 01:38:48 -0500, "DFS" <no****@nospam.com> wrote:

"MGFoster" <[e-mail]> wrote in message

Make sure the Reference...."The Microsoft Office # Object library" is
checked.>

I'm using Access97. Microsoft Access 8.0 Object Library is referenced.

D:

MGF said *Office* Library. You're saying *Access* library.

I played with it on my A97 machine, and checking the Office library
solved the object declaration problem. Prior to checking it, I had
the same behavior you're seeing.

-Matt

[DFS]

"Matthew Sullivan" <Ma**@NoSpam.com> wrote in message
news:1o********************************@4ax.com...

On Mon, 23 Feb 2004 01:38:48 -0500, "DFS" <no****@nospam.com> wrote:

"MGFoster" <[e-mail]> wrote in message

Make sure the Reference...."The Microsoft Office # Object library" is
checked.>
I'm using Access97. Microsoft Access 8.0 Object Library is referenced.

D:

MGF said *Office* Library. You're saying *Access* library.

Yes, I saw that - I figured he was just mistaken. There's no Office 8.0
Library available in my list of references. I have an Access 8.0 library,
an Access 9.0 library, and an Office 10.0 library, but no Office 8.0
library.

I played with it on my A97 machine, and checking the Office library
solved the object declaration problem. Prior to checking it, I had
the same behavior you're seeing.

Checking any two of the above libraries causes a variety of other errors.

Leaving my Access 8.0 lib reference in place, if I dim the variables as
Variants (rather than CommandBars) his code example runs fine; it just
doesn't do what I need.

Thanks

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The code I posted was just a demonstration. You can add new
CommandBar properties to the loop - inside the loop type period (.)
and the IntelliSense will pop-up a list of CommandBar properties.
This will only work if you declare the variable as a CommandBar
object. Usually main menus have an index of ZERO and their submenus
have a higher index number.

It seems you have Office XP (10.0) library installed on your computer.
To get the Office 97 (8.0) library it may be necessary to install
something from the Office 97 (Access 97) CD. When the installation
program runs select the Custom installation & look for something like
Addins or Libraries (DLL files).

Then again, you may already have the Office 97 library in the
Office/Access folder (C:\Program Files\Microsoft Office\Office). Look
for the file mso97.dll.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDrpq4echKqOuFEgEQIT1ACgtZLgKsRULj2hFsH2PwuIuL VdxDUAoL0f
VbqyD8HAm0v3LVhJVZ3UVy+X
=SRuo
-----END PGP SIGNATURE-----
DFS wrote:

< SNIP >

Leaving my Access 8.0 lib reference in place, if I dim the variables as
Variants (rather than CommandBars) his code example runs fine; it just
doesn't do what I need.

[Matthew Sullivan]

D:

OK, then the simple "brute-force" method is just to add
error-handling to get around where your original code was frying.

The function below seems to get past the "no sub-menus" problem. In
real-life I'd probably write something recursive, but this was pretty
easy to spank out just to see how things behaved.

If you want to have some fun, set bEnable to "False". ;-)

This help at all?

-Matt

==================================
Public Function PlayCommandBars()

On Error GoTo ErrorHandler

'declare variables
Dim bEnable As Boolean
Dim theCmdBar As Object
Dim TheCmdBarCtrl As Object
Dim TheCmdBarCtrlCtrl As Object
'Dim theCmdBar As CommandBar
'Dim TheCmdBarCtrl As CommandBarControl
'Dim TheCmdBarCtrlCtrl As CommandBarControl

'initialize variables
bEnable = True
'bEnable = False

For Each theCmdBar In Application.CommandBars
For Each TheCmdBarCtrl In theCmdBar.Controls

TheCmdBarCtrl.Enabled = bEnable

On Error Resume Next
For Each TheCmdBarCtrlCtrl In TheCmdBarCtrl.Controls
TheCmdBarCtrlCtrl.Enabled = bEnable
Next TheCmdBarCtrlCtrl
On Error GoTo ErrorHandler

Next TheCmdBarCtrl
Next theCmdBar


Done:
On Error Resume Next

'free resources
Set TheCmdBarCtrlCtrl = Nothing
Set TheCmdBarCtrl = Nothing
Set theCmdBar = Nothing
Exit Function

ErrorHandler:
MsgBox Err.Number & vbNewLine & Err.Description
Resume Done
End Function

On Tue, 24 Feb 2004 00:39:12 -0500, "DFS" <no****@nospam.com> wrote:

Leaving my Access 8.0 lib reference in place, if I dim the variables as
Variants (rather than CommandBars) his code example runs fine; it just
doesn't do what I need.

Thanks