Conditional menu from add-in

[LaurenceO]

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?

 

[dk]

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:

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.

 

[LaurenceO]

Thanks Dan, I'll give it a try

Laurence