Commandbars dynamic menu creation problem

[GusEvans]

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 Autpen
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!").Controls.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!").Controls("Next Report").Delete
On Error GoTo ErrorHandler
intL = 1
Set cbcNext = Application.CommandBars(1).Controls("ACT!").Controls.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,

 

[Jim Cone]

Gus,
The "Act" control doesn't exist so:
replace...
Set cbcNext = Application.CommandBars(1).Controls("ACT!").Controls.Add
with...
Set cbcNext = Application.CommandBars(1).Controls(Type:=msoControButton).Add
--
Regards,
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"GusEvans"
<[email protected]>
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 Autpen
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!").Controls.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!").Controls("Next Report").Delete
On Error GoTo ErrorHandler
intL = 1
Set cbcNext = Application.CommandBars(1).Controls("ACT!").Controls.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,

 

[GusEvans]

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?

 

[Doug Glancy]

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]

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?

 

[Doug Glancy]

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]

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.