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

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

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

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

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

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

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