Test to see if custom menu exists
 

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:=msoCont rolPopup).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!

Test to see if custom menu exists
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Kent McPherson" wrote in message ...
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:=msoCont rolPopup).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!

Test to see if custom menu exists
 

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:=msoCont rolPopup).Caption =
"Baselines"
end if

--
HTH...

Jim Thomlinson


"Kent McPherson" wrote:

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:=msoCont rolPopup).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!