Conditional menu from add-in

LaurenceO

New Member
Joined
Sep 2, 2002
Messages
2
Had a good search but no luck. Can anyone help on this:

Got all my code in an add-in. It creates/deletes a custom menu item perfectly nicely on loading and closing. However I want that menu to be visible or enabled only when a specific named worksheet is loaded (and obviously the converse when it's closed).

I can figure this out by adding code to the worksheet, but I need it to be code free. So I guess what I'm asking for is an auto-sensing sort of thing that is run from the (loaded) add-in and checks a new worksheet open/close event.

Possible?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
On 2002-09-03 16:16, LaurenceO wrote:
Had a good search but no luck. Can anyone help on this:

Got all my code in an add-in. It creates/deletes a custom menu item perfectly nicely on loading and closing. However I want that menu to be visible or enabled only when a specific named worksheet is loaded (and obviously the converse when it's closed).

I can figure this out by adding code to the worksheet, but I need it to be code free. So I guess what I'm asking for is an auto-sensing sort of thing that is run from the (loaded) add-in and checks a new worksheet open/close event.

Possible?

Hello,

Possible, yes. You need to use application level events which you can use to trap events such as a new workbook being opened. Here is a useful resource:-

http://www.cpearson.com/excel/AppEvent.htm

Set up your add-in so it traps workbook events as Chip suggests. In your class module you'll need something like this:

Code:
Private Sub App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
'Disable the menu item if the workbook is deactivated
If Wb.Name = "The workbook in question" Then
    'Disable your menu item
End If
End Sub

Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
'Enable menu item if the workbook is activated
If Wb.Name = "The workbook in question" Then
    'Enable your menu item
End If
End Sub

I hope that provides you with enough info to get it working. Please post back if you have any more questions.
 

Forum statistics

Threads
1,144,330
Messages
5,723,749
Members
422,513
Latest member
Meathead2022

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top