Disable Menu Item Based on ActiveCell Formula

[edpolanco10]

I have and excel addin for 2003 the adds a menu item to the right click menu. The addin works just find but I want to add the functionality where it is only enabled when the activecell contains a certain formula, lets call it “MyFunc()”. I know how to read the active formula from the active cell. I have a procedure that reads the formula. I also have a procedure that disables my menu item. But how can I get excel to call this procedure to disable my menu item when the activecell contains “MyFunc()”. I tried using the BeforeRightClick event in the thisworkbook but that only works on the active workbook.

 

[Rick Rothstein]

It sounds like you would want to use the SelectionChange event for the worksheet, not the BeforeRightClick. Simply check Target.Formula to see if it equals the formula you want to look for and, if it does, disable the menu item, otherwise enable it. Something like this I would imagine...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Formula = "=MyFunc()" Then
    ' Code to enable the menu item
  Else
    ' Code to disable the menu item
  End If
End Sub

Remember, the If..Then test is case sensitive, so if you are testing against a UDF (user defined function) name, Excel won't automatically upper case the name like it does for its built-in functions, so you might want to so the test this way to be sure you account for variations in how a user types the function...

If UCase(Target.Formula) = "=MYFUNC()" Then

 

[edpolanco10]

Rick, Thanks for you reply. Putting the coded in SelectionChange event does work but only with the active workbook. When I put the code in the addin's SelectionChange event it doesn't work. Are the events procedures not available when the file is converted to an Addin?

 

[Rick Rothstein]

When I put the code in the addin's SelectionChange event it doesn't work.

Ah, AddIn... I missed that when I read your original message. I've not worked with them before, so I can only guess here. What if you put it in the AddIn's ThisWorkbook's SheetSelectionChange event... will that reach across to the ActiveWorkbook?

 

[edpolanco10]

I tried putting the code in the Addin file this workbook SheetSelectionChange event but it didn't work.

 

[Rick Rothstein]

I tried putting the code in the Addin file this workbook SheetSelectionChange event but it didn't work.

As I said, I have not done this myself, but a Google search turned up the following link which it looks like should give you a "template" on how to proceed...

http://stackoverflow.com/questions/858691/how-can-an-excel-add-in-respond-to-events-in-any-worksheet

 

[edpolanco10]

I got it! Thanks Rick.
With the help from that link I was able to figure it out. I essentially created a Class Module in my Add-In that encapsulates an Excel Application Object. Within the class module I used the SheetBeforeRightClick event to disable and enable my menu item depending on the formula contained in the ActiveCell. When the Add-In opens it instantiates Class Module by using the Workbook_Open() event. Below is simplified version of code
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Add-In Code:
Dim MyMenuHandler As CMyClassModule<o></o>
Private Sub Workbook_Open()<o></o>
Set MyMenuHandler = New CMyClassModule<o></o>
End Sub
<o> </o>
Class Module:
Option Explicit<o></o>
Private WithEvents App As Application<o></o>
Private Sub App_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)<o></o>
If ActiveCell.Formula = "=MyFunc()" Then<o></o>
'enable menu code<o></o>
Else<o></o>
'disable menu code<o></o>
End If<o></o>
End Sub<o></o>
<o> </o>
Private Sub Class_Initialize()<o></o>
Set App = Application<o></o>
End Sub
<o> </o>

 

[Rick Rothstein]

I got it! Thanks Rick.
With the help from that link I was able to figure it out.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Great! And you are quite welcome, of course. I thought the material at that link looked like it my give you a good head's up on how to do what you wanted.