Hi all,

I have a addin which sets up an option on the main menu bar named
'Toolz' - the code is below... this works ok. I also have similar code
in each worksheet to add a menu item relevant to that worksheet -
under the 'Toolz' option.

My aim was to activate and deactivate a menu option when the user
flicked through different workbooks using the same instance of Excel.

Here is the code I use in the addin...

Set myMnu = CommandBars("Worksheet menu bar").Controls. _
Add(Type:=msoControlPopup)
With myMnu
.Caption = "Toolz"
End With

However, when I use the following code within ThisWorkBook...

Set newSub = CommandBars("Worksheet menu bar") _
.Controls("Toolz")
With newSub
.Controls.Add(Type:=msoControlPopup).Caption = "St: Incidents"
End With

I get 'Run time error 91: Object variable or With variable not set'.

Now I've condensed the code, but whichever way I try and duplicate
what I've done in the sheet modules - I cannot get anything like it to
work in the ThisWorkBook module.

I know there is a subtle difference between sheet modules and the
workbook module. Can someone please explain why the exect same code
will work in a sheet module but not in the workbook module?

Thanks,
Dave

Dave,
You must prefix CommandBars with Application. when referring
to them in class modules. It is good practice to do that no matter
what type of module your code is in.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Dave" <(E-Mail Removed)>
wrote in message
Hi all,

I have a addin which sets up an option on the main menu bar named
'Toolz' - the code is below... this works ok. I also have similar code
in each worksheet to add a menu item relevant to that worksheet -
under the 'Toolz' option.

My aim was to activate and deactivate a menu option when the user
flicked through different workbooks using the same instance of Excel.

Here is the code I use in the addin...

Set myMnu = CommandBars("Worksheet menu bar").Controls. _
Add(Type:=msoControlPopup)
With myMnu
.Caption = "Toolz"
End With

However, when I use the following code within ThisWorkBook...

Set newSub = CommandBars("Worksheet menu bar") _
.Controls("Toolz")
With newSub
.Controls.Add(Type:=msoControlPopup).Caption = "St: Incidents"
End With

I get 'Run time error 91: Object variable or With variable not set'.
Now I've condensed the code, but whichever way I try and duplicate
what I've done in the sheet modules - I cannot get anything like it to
work in the ThisWorkBook module.
I know there is a subtle difference between sheet modules and the
workbook module. Can someone please explain why the exect same code
will work in a sheet module but not in the workbook module?
Thanks,
Dave

On Tue, 30 Jan 2007 09:38:28 -0800, "Jim Cone"
<(E-Mail Removed)> wrote:

Thanks for that Jim, it worked fine.

Dave

>Dave,
>You must prefix CommandBars with Application. when referring
>to them in class modules. It is good practice to do that no matter
>what type of module your code is in.