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.
Similar Threads
