JohnnyBGood
12-17-2010, 08:10 AM
Hi,
I have a spreadsheet that displays results of a database query (using MsQuery)
It uses wildcard parameters in range A2:E2.
The user can input phrases/keyworkds in any or all of those cells and the query will automatically refresh with those criteria.
The problem now is that when a user empties all those cells, the query tries to refresh with no criteria. Our database is quite large and this could take a while to refresh.
I am not sure of an alternative, but I figured an event macro should do it.
I want to say in the macro that if none of the cells have an entry, send a message and do not continue with the refreshing.
This is the code I have which partially works.
The problem with it is that it tries to refresh the macro if I just click in any of those target cells A2:E2.
I only want it to trigger the macro after somebody enters any new information in any of those cells.. and don't trigger if I just click in a cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A2:E2")) Is Nothing Then
If Application.CountA(Range("A2:E2")) > 0 Then
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Else
MsgBox "You need to have at least one search criteria", vbCritical, "Error"
End If
End If
End Sub
How do I change the above?
I tried playing with Application.EnableEvents... but could not get it right.
Thanks.
I have a spreadsheet that displays results of a database query (using MsQuery)
It uses wildcard parameters in range A2:E2.
The user can input phrases/keyworkds in any or all of those cells and the query will automatically refresh with those criteria.
The problem now is that when a user empties all those cells, the query tries to refresh with no criteria. Our database is quite large and this could take a while to refresh.
I am not sure of an alternative, but I figured an event macro should do it.
I want to say in the macro that if none of the cells have an entry, send a message and do not continue with the refreshing.
This is the code I have which partially works.
The problem with it is that it tries to refresh the macro if I just click in any of those target cells A2:E2.
I only want it to trigger the macro after somebody enters any new information in any of those cells.. and don't trigger if I just click in a cell.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A2:E2")) Is Nothing Then
If Application.CountA(Range("A2:E2")) > 0 Then
Range("A4").QueryTable.Refresh BackgroundQuery:=False
Else
MsgBox "You need to have at least one search criteria", vbCritical, "Error"
End If
End If
End Sub
How do I change the above?
I tried playing with Application.EnableEvents... but could not get it right.
Thanks.