Declaring Command Bar Control
 

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.
--
I am running on Excel 2003, unless otherwise stated. Please rate posts so we
know when we have answered your questions. Thanks.

Declaring Command Bar Control
 

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.
--
I am running on Excel 2003, unless otherwise stated. Please rate posts so we
know when we have answered your questions. Thanks.

Declaring Command Bar Control
 

Thanks. I didn't know about set. When is it used?
--
I am running on Excel 2003, unless otherwise stated. Please rate posts so we
know when we have answered your questions. Thanks.


"Jim Cone" wrote:

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.
--
I am running on Excel 2003, unless otherwise stated. Please rate posts so we
know when we have answered your questions. Thanks.

Declaring Command Bar Control
 

"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?
--

Declaring Command Bar Control
 

OK. Thanks. I have only declared strings and VbMsgBoxResults. This is my
first attempt at declaring an object. Lots to learn.
--
I am running on Excel 2003, unless otherwise stated. Please rate posts so we
know when we have answered your questions. Thanks.


"Jim Cone" wrote:


"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?
--