how do I ensure enter is pressed

  • Thread starter Thread starter busyman5_au
  • Start date Start date
B

busyman5_au

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
 
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
 
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
 
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
 
Back
Top