Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbars dynamic menu creation problem
Hi-
Problem have a large workbook/macro that the user needs to open occasionaly. I am trying to create a small sized workbook (ACT-Menu.xls) to open every time Excel starts, add one menu Item to an existing menu and poit to a local macro. This macro merely opens the worksheet and starts an Auto_Open subroutine in the newly opened macro. I have ACT-Menu.xls protected, hidden and stored in the XLStart Folder. On opening Excel it gives an error on the following line - Set cbcNext = Application.CommandBars(1).Controls("ACT!").Contro ls.Add Macro code below If I open the macro in the VB Editor, I can step thru it without error. What's the problem?? Option Explicit Private Sub Workbook_Open() Dim cbcNext As CommandBarControl Dim strErr As String Dim intL As Integer On Error Resume Next Application.CommandBars(1).Controls("ACT!").Contro ls("Next Report").Delete On Error GoTo ErrorHandler intL = 1 Set cbcNext = Application.CommandBars(1).Controls("ACT!").Contro ls.Add intL = 2 cbcNext.Caption = "&Next Report" intL = 3 cbcNext.OnAction = "OpenACTReports" intL = 4 cbcNext.BeginGroup = True intL = 5 Workbooks.Add Exit Sub ErrorHandler: strErr = "Error # " & Str(Err.Number) & " was generated by " _ & Err.Source & Chr(13) & Err.Description & Chr(13) & _ "for line # " & Str(intL) MsgBox strErr, , "Error", Err.HelpFile, Err.HelpContext End Sub Thanks in advance, -- Gus Evans |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbars dynamic menu creation problem
Gus,
The "Act" control doesn't exist so: replace... Set cbcNext = Application.CommandBars(1).Controls("ACT!").Contro ls.Add with... Set cbcNext = Application.CommandBars(1).Controls(Type:=msoContr oButton).Add -- Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "GusEvans" wrote in message... Hi- Problem have a large workbook/macro that the user needs to open occasionaly. I am trying to create a small sized workbook (ACT-Menu.xls) to open every time Excel starts, add one menu Item to an existing menu and poit to a local macro. This macro merely opens the worksheet and starts an Auto_Open subroutine in the newly opened macro. I have ACT-Menu.xls protected, hidden and stored in the XLStart Folder. On opening Excel it gives an error on the following line - Set cbcNext = Application.CommandBars(1).Controls("ACT!").Contro ls.Add Macro code below If I open the macro in the VB Editor, I can step thru it without error. What's the problem?? Option Explicit Private Sub Workbook_Open() Dim cbcNext As CommandBarControl Dim strErr As String Dim intL As Integer On Error Resume Next Application.CommandBars(1).Controls("ACT!").Contro ls("Next Report").Delete On Error GoTo ErrorHandler intL = 1 Set cbcNext = Application.CommandBars(1).Controls("ACT!").Contro ls.Add intL = 2 cbcNext.Caption = "&Next Report" intL = 3 cbcNext.OnAction = "OpenACTReports" intL = 4 cbcNext.BeginGroup = True intL = 5 Workbooks.Add Exit Sub ErrorHandler: strErr = "Error # " & Str(Err.Number) & " was generated by " _ & Err.Source & Chr(13) & Err.Description & Chr(13) & _ "for line # " & Str(intL) MsgBox strErr, , "Error", Err.HelpFile, Err.HelpContext End Sub Thanks in advance, -- Gus Evans |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbars dynamic menu creation problem
Jim -
1. Because I am using the program ACT!, Excel always has an ACT! menu. 2. I copied and tried your code and it didn't work, came up with Compile Errors. 3. As I mentioned I can open the VBE and step through the code and it works fine. Any other suggestions? -- Gus Evans "Jim Cone" wrote: Gus, The "Act" control doesn't exist so: replace... Set cbcNext = Application.CommandBars(1).Controls("ACT!").Contro ls.Add with... Set cbcNext = Application.CommandBars(1).Controls(Type:=msoContr oButton).Add -- Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbars dynamic menu creation problem
Gus,
Is it possible that your XLStart workbook is opening before whatever process (add-in?) creates the ACT menu? If you want to test this, try putting a Stop statement in the XLStart workbook. This will put you into Debug mode as it loads and you can check whether the ACT menu has been created at yet. hth, Doug "GusEvans" wrote in message ... Jim - 1. Because I am using the program ACT!, Excel always has an ACT! menu. 2. I copied and tried your code and it didn't work, came up with Compile Errors. 3. As I mentioned I can open the VBE and step through the code and it works fine. Any other suggestions? -- Gus Evans "Jim Cone" wrote: Gus, The "Act" control doesn't exist so: replace... Set cbcNext = Application.CommandBars(1).Controls("ACT!").Contro ls.Add with... Set cbcNext = Application.CommandBars(1).Controls(Type:=msoContr oButton).Add -- Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbars dynamic menu creation problem
Doug -
You are correct, I opened Excel while the system was very busy with other tasks and, after the error message appeared and was closed the ACT! and Adobe PDF menu items appeared. So what can I do to wait or loop until it appears? -- Gus Evans "Doug Glancy" wrote: Gus, Is it possible that your XLStart workbook is opening before whatever process (add-in?) creates the ACT menu? If you want to test this, try putting a Stop statement in the XLStart workbook. This will put you into Debug mode as it loads and you can check whether the ACT menu has been created at yet. hth, Doug "GusEvans" wrote in message ... Jim - 1. Because I am using the program ACT!, Excel always has an ACT! menu. 2. I copied and tried your code and it didn't work, came up with Compile Errors. 3. As I mentioned I can open the VBE and step through the code and it works fine. Any other suggestions? -- Gus Evans "Jim Cone" wrote: Gus, The "Act" control doesn't exist so: replace... Set cbcNext = Application.CommandBars(1).Controls("ACT!").Contro ls.Add with... Set cbcNext = Application.CommandBars(1).Controls(Type:=msoContr oButton).Add -- Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbars dynamic menu creation problem
Gus,
I looked at your original post and am not sure I understand what you are trying to do. Can you do your menu modifications when the "large workbook/macro that the user needs to open occasionally" is opened, by using an AutoOpen or WorkbookOpen event in that workbook? Doug "GusEvans" wrote in message ... Doug - You are correct, I opened Excel while the system was very busy with other tasks and, after the error message appeared and was closed the ACT! and Adobe PDF menu items appeared. So what can I do to wait or loop until it appears? -- Gus Evans "Doug Glancy" wrote: Gus, Is it possible that your XLStart workbook is opening before whatever process (add-in?) creates the ACT menu? If you want to test this, try putting a Stop statement in the XLStart workbook. This will put you into Debug mode as it loads and you can check whether the ACT menu has been created at yet. hth, Doug "GusEvans" wrote in message ... Jim - 1. Because I am using the program ACT!, Excel always has an ACT! menu. 2. I copied and tried your code and it didn't work, came up with Compile Errors. 3. As I mentioned I can open the VBE and step through the code and it works fine. Any other suggestions? -- Gus Evans "Jim Cone" wrote: Gus, The "Act" control doesn't exist so: replace... Set cbcNext = Application.CommandBars(1).Controls("ACT!").Contro ls.Add with... Set cbcNext = Application.CommandBars(1).Controls(Type:=msoContr oButton).Add -- Regards, Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbars dynamic menu creation problem
Doug -
The answer to your specific question is Yes. I tried to use tour Stop code and added a lood with a Wait until the ACT! menu showed up, but just got into a loop that I couldn't kill with a Ctrl-Alt-Delete ;-( Finally I added a tool to the toolbar that would run the OpenReports macro in the ACT-Menu workbook (+ a couple of other fixes) and that works ;-) Still can't understand how to make Excel hesitate until all the menus show up. Thanks for all of your help - it lead in the right direction. -- Gus Evans "Doug Glancy" wrote: Gus, I looked at your original post and am not sure I understand what you are trying to do. Can you do your menu modifications when the "large workbook/macro that the user needs to open occasionally" is opened, by using an AutoOpen or WorkbookOpen event in that workbook? Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Formula Creation?? | Excel Discussion (Misc queries) | |||
ASP Dynamic Excel Creation | Excel Programming | |||
Menu creation - from DLL rather than .XLA | Excel Programming | |||
Dynamic Control Creation in VBA | Excel Programming | |||
Dynamic Creation of borders | Excel Programming |