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!").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,
--
Gus Evans

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 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!").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,
--
Gus Evans

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!").Controls.Add
> with...
> Set cbcNext = Application.CommandBars(1).Controls(Type:=msoControButton).Add
> --
> Regards,
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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!").Controls.Add
>> with...
>> Set cbcNext =
>> Application.CommandBars(1).Controls(Type:=msoControButton).Add
>> --
>> Regards,
>> Jim Cone
>> San Francisco, USA
>> http://www.realezsites.com/bus/primitivesoftware
>>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!").Controls.Add
> >> with...
> >> Set cbcNext =
> >> Application.CommandBars(1).Controls(Type:=msoControButton).Add
> >> --
> >> Regards,
> >> Jim Cone
> >> San Francisco, USA
> >> http://www.realezsites.com/bus/primitivesoftware
> >>
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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!").Controls.Add
>> >> with...
>> >> Set cbcNext =
>> >> Application.CommandBars(1).Controls(Type:=msoControButton).Add
>> >> --
>> >> Regards,
>> >> Jim Cone
>> >> San Francisco, USA
>> >> http://www.realezsites.com/bus/primitivesoftware
>> >>
>> >
>>
>>
>>