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


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


"Kent McPherson" <[email protected]> wrote in message news:[email protected]...
>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!
>
>

[Jim Thomlinson]

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

--
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:=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!
>
>
>