Lars,
Application.EnableEvents should be true, otherwise events (such as changes)
are ignored. If you have not been setting it to "off" in VBA to achieve
certain results, then that probably is not the source of your problem. (It
is reset when the Excel is restarted).
How about this:
In order to have the macro run after the other program (what is it, by the
way?) pastes to the Excel sheet, the following options seem logical:
1. Have the external application launch an Excel macro
2. Try to "fake" the event, or infer it
3. Have a button available for the user to manually kick off the macro.
I'll assume that 1 and 3 are yours
As far as 'faking' the event, two ideas come to mind:
1. Use the WorkSheet_SelectionChange Event to verify if new data has arrived
(try it)
2, Use the calculate event in the same way.
In order to detect if data has changed on the sheet, would it be reasonable
to assume that the sheet was previously (mostly) blank, and new pasted data
drastically increases the size of the used range? Why not try this program
on the Sheet Object:
Sub test()
Static n1 As Long
Dim nn As Long
nn = Application.WorksheetFunction.Subtotal(3, Me.UsedRange)
If nn <> n1 Then
Msgbox "Found a Change"
n1 = nn
End If
End Sub
n1 is static, it holds its value between execution cycles of the program
The function is pretty fast
Instead of using "3" in the subtotal to count cells, try "9" to sum cell
values?
Hope this points in a creative direction for you.
Alex J
