Test to see if custom menu exists

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kent McPherson - 22 Feb 2006 18:37 GMT

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!

Reply to this Message

Ron de Bruin - 22 Feb 2006 19:05 GMT

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

Signature

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

>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:
[quoted text clipped - 6 lines]
>
> Thanks!

Reply to this Message

Jim Thomlinson - 22 Feb 2006 19:13 GMT

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

Signature

HTH...

Jim Thomlinson

> 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
[quoted text clipped - 11 lines]
>
> Thanks!

Reply to this Message