As far as I know, that has always been the way XL has worked. No VBA
code can run as long as the user is editing a cell.

You could make things more complicated for yourself by disabling the
button until the cell of interest is changed. In the example below, I
assumed that there can be two criteria, specified in E5:F5. The button
next to the criteria range is called CommandButton1

Option Explicit

Private Sub CommandButton1_Click()
MsgBox "hello"
Me.CommandButton1.Enabled = False
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, _
Target.Parent.Range("e5:F5")) Is Nothing Then
Exit Sub
End If
Me.CommandButton1.Enabled = True
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , busyman5_au
says...
They are entering a value into the cell set up for the criteria, in this
case C7.

So at the moment I have a pair of cells holding criteria (filter column
label and value) and next to it a nice big button saying APPLY FILTER.
But unless thee the user moves off the criteria value cell there is
no effect when they invoke the macro. Which is not very intuitive for
novice users.

Thanks for continued thinking on this matter.


---
Message posted from http://www.ExcelForum.com/