Detect Custom Menu
 

I've got my vba code creating a custom menu called "mymenu". The code only
loads the menu when certain spreadsheets open containing my criteria.

What I can't figure out is how to detect if "mymenu" has already been loaded
and if it has, don't load it a 2nd time. This happens sometimes because the
user opens up several spreads that contain my special criteria in certain
cells.

Any ideas?

Detect Custom Menu
 

Hi Scott

One way

On the first line of the macro that create the menu delete your menu firs if it exist

On Error Resume Next
Application.CommandBars("MyCustomMenu").Delete
On Error GoTo 0

Then create your menu



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



"scott" wrote in message ...
I've got my vba code creating a custom menu called "mymenu". The code only loads the menu when certain spreadsheets open
containing my criteria.

What I can't figure out is how to detect if "mymenu" has already been loaded and if it has, don't load it a 2nd time. This happens
sometimes because the user opens up several spreads that contain my special criteria in certain cells.

Any ideas?

Detect Custom Menu
 

If I open spreadsheet 1, menu loads. If I close spreadsheet 1, menu remains
if excel is left open. If I re-open same or another spreadsheet needing the
menu, it loads a 2nd instance of the menu.

I'm running all code from an add-in I created. I can't place any code in the
spreadsheets because they are machine created.

I'm now wondering if their is a way for an add-in to detect or fire an event
when a spreadsheet that contains certain criteria is being closed.

Do you have an opinion?


"Ron de Bruin" wrote in message
...
Hi Scott

One way

On the first line of the macro that create the menu delete your menu firs
if it exist

On Error Resume Next
Application.CommandBars("MyCustomMenu").Delete
On Error GoTo 0

Then create your menu



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



"scott" wrote in message
...
I've got my vba code creating a custom menu called "mymenu". The code
only loads the menu when certain spreadsheets open containing my
criteria.

What I can't figure out is how to detect if "mymenu" has already been
loaded and if it has, don't load it a 2nd time. This happens sometimes
because the user opens up several spreads that contain my special
criteria in certain cells.

Any ideas?

Detect Custom Menu
 

I got it fixed, I was over looking the obvious. thanks.


"Ron de Bruin" wrote in message
...
Hi Scott

One way

On the first line of the macro that create the menu delete your menu firs
if it exist

On Error Resume Next
Application.CommandBars("MyCustomMenu").Delete
On Error GoTo 0

Then create your menu



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



"scott" wrote in message
...
I've got my vba code creating a custom menu called "mymenu". The code
only loads the menu when certain spreadsheets open containing my
criteria.

What I can't figure out is how to detect if "mymenu" has already been
loaded and if it has, don't load it a 2nd time. This happens sometimes
because the user opens up several spreads that contain my special
criteria in certain cells.

Any ideas?

Detect Custom Menu
 

One way:

Dim mnMyMenu As CommandBarControl
On Error Resume Next
Set mnMyMenu = CommandBars.FindControl(Tag:="my menu")
On Error GoTo 0
If mnMyMenu Is Nothing Then
With CommandBars(1).Controls
Set mnMyMenu = .Add(Type:=msoControlPopup, _
Befo=.Count, Temporary:=True)
End With
With mnMyMenu
.Caption = "mymenu"
.Tag = "my menu"
'...
End With
End If





In article ,
"scott" wrote:

I've got my vba code creating a custom menu called "mymenu". The code only
loads the menu when certain spreadsheets open containing my criteria.

What I can't figure out is how to detect if "mymenu" has already been loaded
and if it has, don't load it a 2nd time. This happens sometimes because the
user opens up several spreads that contain my special criteria in certain
cells.

Any ideas?