Declaring Command Bar Control

O

Orion Cochrane

I have a custom menu that is enabled under certain circumstances. I just want
to clean up my code a bit, so I wanted to declare a commandbar control at the
outset of my procedure, and I keep getting an "Invalid use of property"
error. Here is my code:
Sub MenuEnable()
'Enables / disables Reports submenus
On Error GoTo NoFile
Dim spath As String
spath = ActiveWorkbook.Path
If Left(spath, 22) = "<path>" Or _
Left(spath, 28) = "<path>" Then
CommandBars(1).Controls("Main").Controls("Control1").Enabled = True
Else: CommandBars(1).Controls("Main").Controls("Control1").Enabled = False
End If
If spath = "<path>" Then
CommandBars(1).Controls("Main").Controls("Control2").Enabled = True
Else: CommandBars(1).Controls("Main").Controls("Control2").Enabled = False
End If
Exit Sub
NoFile:
MsgBox "There is no active workbook open.", vbCritical, "Error: Main Menu"
End Sub

This macro is triggered when I click on "Main" in the Worksheet Menu Bar
(CommandBars(1)). If there is no active workbook open, the error handler
kicks in (it kicks in probably under other circumstances, but this is the
only one I can think of). The constant is CommandBars(1).Controls("Main").
How do I go about declaring this so I can clean up this code?

I tried: (Dim rpt as...)
CommandBar
CommandBars
CommandBarControl
CommandBarControls

I do not have any formal training in VBA, and any macros I write are a
result of experimentation (which gets me very far, but even I know when I am
beat).

The code presented here works. I hope I explained it enough. Thanks in
advance.
 
J

Jim Cone

Dim rpt As CommandBarControl
Set rpt = Application.CommandBars(1).Controls("Main")
--
Jim Cone
Portland, Oregon USA



"Orion Cochrane"
wrote in message
I have a custom menu that is enabled under certain circumstances.
I just want to clean up my code a bit, so I wanted to declare a
commandbar control at the outset of my procedure,
and I keep getting an "Invalid use of property" error.
Here is my code:
Sub MenuEnable()
'Enables / disables Reports submenus
On Error GoTo NoFile
Dim spath As String
spath = ActiveWorkbook.Path
If Left(spath, 22) = "<path>" Or _
Left(spath, 28) = "<path>" Then
CommandBars(1).Controls("Main").Controls("Control1").Enabled = True
Else: CommandBars(1).Controls("Main").Controls("Control1").Enabled = False
End If
If spath = "<path>" Then
CommandBars(1).Controls("Main").Controls("Control2").Enabled = True
Else: CommandBars(1).Controls("Main").Controls("Control2").Enabled = False
End If
Exit Sub
NoFile:
MsgBox "There is no active workbook open.", vbCritical, "Error: Main Menu"
End Sub

This macro is triggered when I click on "Main" in the Worksheet Menu Bar
(CommandBars(1)). If there is no active workbook open, the error handler
kicks in (it kicks in probably under other circumstances, but this is the
only one I can think of). The constant is CommandBars(1).Controls("Main").
How do I go about declaring this so I can clean up this code?

I tried: (Dim rpt as...)
CommandBar
CommandBars
CommandBarControl
CommandBarControls

I do not have any formal training in VBA, and any macros I write are a
result of experimentation (which gets me very far,
but even I know when I am beat).
The code presented here works. I hope I explained it enough.
Thanks in advance.
 
J

Jim Cone

"Set" is required for all objects.
It tells Excel which object the variable refers to.
--
Jim Cone
Portland, Oregon USA



"Orion Cochrane"
wrote in message
Thanks. I didn't know about set. When is it used?
--
 
O

Orion Cochrane

OK. Thanks. I have only declared strings and VbMsgBoxResults. This is my
first attempt at declaring an object. Lots to learn.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top