Commandbar menu, worksheet modules and workbook module.

[Dave]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[Dave]

On Tue, 30 Jan 2007 09:38:28 -0800, "Jim Cone"

Thanks for that Jim, it worked fine.

Dave