Commandbar menu, worksheet modules and workbook module.

  • Thread starter Thread starter Dave
  • Start date Start date
D

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
 
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
 
Back
Top