If you press F2 to edit a formula in the Formula Bar, a little green
check mark appears that you can click to 'Enter' the edited formula.

I am trying to trap the formula entry event using
Application.OnKey "~", "MySub"

This works fine as long as the user presses the Enter key to enter the
formula. It doesn't work if the user presses the green check mark.

Is there a way to trap the pressing of the green check mark using
OnKey?

Ordinarily, I'd use Workbook_SheetChange or a related event to trap
this. However, the application I'm writing will reside in a separate
workbook (add-in) from the workbook the user is actually editing. So
I can't assume or use any of the workbook/worksheets events in the
workbook being edited.

In your addin, put in a class module (assume class1)

in the class module put:

Public WithEvents wkbk As Workbook


Private Sub wkbk_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
MsgBox Sh.Name & " " & vbNewLine & _
Target.Address(external:=True)
End Sub

Now, in a general module in your add in put in:

Dim theBook As New Class1

Sub SetClass()
Set theBook.wkbk = Workbooks("Button10.xls")
End Sub

Where Button10.xls is the workbook where I want to trap the change event.

Now, when there is an edit performed in Button10.xls, the workbook level
change event in the class module in your addin will fire. It does detect
the problem you cite.

Not sure when you want to trap this, so you have to figure out how to call
SetClass and tell it what workbook. If you want to trap changes in all
workbooks, then you need to instantiate application level events in your
addin's workbook_open event.

see Chip Pearson's page on this:

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

What happended to those Hokies?

Know anybody with my last name down there?

--
Regards,
Tom Ogilvy



Cliff Ragsdale wrote in message
om...
If you press F2 to edit a formula in the Formula Bar, a little green
check mark appears that you can click to 'Enter' the edited formula.

I am trying to trap the formula entry event using
Application.OnKey "~", "MySub"

This works fine as long as the user presses the Enter key to enter the
formula. It doesn't work if the user presses the green check mark.

Is there a way to trap the pressing of the green check mark using
OnKey?

Ordinarily, I'd use Workbook_SheetChange or a related event to trap
this. However, the application I'm writing will reside in a separate
workbook (add-in) from the workbook the user is actually editing. So
I can't assume or use any of the workbook/worksheets events in the
workbook being edited.