I have a simple macro (below) that invokes the advanced filter criteria
but it fails to refresh if the user is still "in" the filed wher
criteria is being input.

How can I ensure that the data entry field has had an ENTER or TA
pressed before the rest of the macro runs.

Thanks, Simon

Sub mh_apply_filter()
'
' mh_apply_filter Macro
' To allow refresh of the filtering. Macro recorded 27/02/2004 b
administrator
'
'
Range("F10").Select
Range("A10:AB210").AdvancedFilter Action:=xlFilterInPlace
CriteriaRange:= _
Range("C7:C8"), Unique:=False
End Su

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

If the user is editing a worksheet cell, all VBA functionality is
suspended. So, your macro cannot start until the user completes data
entry.

If the user is editing something else, you have to explain what, where,
and how.

--
Regards,

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

In article , busyman5_au
says...
I have a simple macro (below) that invokes the advanced filter criteria,
but it fails to refresh if the user is still "in" the filed where
criteria is being input.

How can I ensure that the data entry field has had an ENTER or TAB
pressed before the rest of the macro runs.

Thanks, Simon

Sub mh_apply_filter()
'
' mh_apply_filter Macro
' To allow refresh of the filtering. Macro recorded 27/02/2004 by
administrator
'
'
Range("F10").Select
Range("A10:AB210").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("C7:C8"), Unique:=False
End Sub


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

They are entering a value into the cell set up for the criteria, in thi
case C7.

So at the moment I have a pair of cells holding criteria (filter colum
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 i
no effect when they invoke the macro. Which is not very intuitive fo
novice users.

Thanks for continued thinking on this matter

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

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/