Test to see if custom menu exists

[Kent McPherson]

I have a spreadsheet where I create a custom menu added to the Tools menu
when the workbook is opened. In the ThisWorkBook object, in the Sub
Workbook_Open(), I have code that looks something like:

Application.CommandBars("Worksheet men
bar").Controls("Tools").Controls.Add(Type:=msoControlPopup).Caption =
"Baselines"

The menu is deleted when the workbook is closed by the Sub
Workbook_BeforeClose(Cancel As Boolean) routine. This all works great.
But if I open another workbook that has different data but the same macros,
I get another copy of the Baselines menu. How can I check to see if the
menu already exists and skip the creation/deletion step appropriately?

Thanks!

 

[Ron de Bruin]

Hi Kent

You can delete the menu first before you add it
Use a On Error in your code so it not blow if it not exist


On Error Resume Next

'code to delete the menu

On Error GoTo o

'code to create the menu

 

[Guest]

set an object to the button and then check to see if the object exists or
not... something like this...

dim cmd as commandbarcontrol

on error resume next
set cmd = Application.CommandBars("Worksheet
menubar").Controls("Tools").Controls("Baselines")
on error goto 0

if cmd is nothing then
Application.CommandBars("Worksheet men
bar").Controls("Tools").Controls.Add(Type:=msoControlPopup).Caption =
"Baselines"
end if