Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command Button Control Properties | Excel Discussion (Misc queries) | |||
Control box/Command buttons | Excel Programming | |||
Macro command control by date | Excel Worksheet Functions | |||
control command | Excel Discussion (Misc queries) | |||
Command vs Control Buttons | Excel Programming |