A small problem when adding custom menus and switching to other workbooks.

[Dave]

Hi,

I've created a addin which loads up a skeleton menu which (obv.) can
be used with any workbook. This works fine.

On a particular workbook, each sheet loads (and unloads) its own menu
on the Worksheet_Activate and _Deactivate methods. This works fine
too.

My problem is this... when a particular sheet has its' menu loaded and
I switch to another workbook, the previous sheets' menu is still
loaded, visible and available to run.

Is there any way to remove a submenu when a particular workbook has
lost its' focus? I'd rather not have to code evey other workbook to
remove it - I'm hoping I can keep all the code within the same place.

Many thanks,
Dave

 

[Dave Peterson]

Maybe you can tie into the workbook events:

Private Sub Workbook_Activate()
Private Sub Workbook_Deactivate()

(Under ThisWorkbook)

 

[Dave]

On Thu, 25 Jan 2007 11:21:06 -0600, Dave Peterson

Hi Dave,

Thanks for the reply.

If I'm not misunderstanding this - each of my sheets has a Deactivate
event which removes its' menu, but if the sheet is active and the user
simply switches to another workbook on the taskbar, the Deactivate
event doesn't 'kick in'.

Perhaps it's because it is still active in its' own workbook.

I need to detect the fact that the user has changed workbooks.

Ideally, that the user has changed workbooks and has left a active
sheet in another workbook - something like the worksheet has lost
focus, but keeping all the code within the old sheet so it has
complete control of loading and unloading its' own menu.

Does this make sense?

Regards,
Dave

 

[Dave Peterson]

I'm guessing that the icons on the taskbar are still workbooks open in the same
instance of excel, right?

Then take a look at the workBOOK_deactivate and workBOOK_activate events. These
are the events that fire when you're swapping workbooks.

These are different than the worksheet events.

 

[Dave]

On Thu, 25 Jan 2007 12:10:02 -0600, Dave Peterson

Hi Dave,

Yes you guessed right.

Many thanks,
Dave