Solved: Event Macro to trigger if a query should refresh or not

[JohnnyBGood]

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.

[vba]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[/vba]
How do I change the above?
I tried playing with Application.EnableEvents... but could not get it right.

Thanks.

[p45cal]

try working with the
Worksheet_Change
event instead.

[JohnnyBGood]

Thanks for that. It works far better now.

The only issue that remains is that if there are no entries made, the warning message comes up as intended, but after clicking OK, it still refreshes the query with all records. What do I need to add to ensure it does not refresh the query if no entries are made in A2:E2 and OK is pressed upon the message popping up?

[Jan Karel Pieterse]

You could uncheck the box on each parameter that says "Refresh automatically when Cell value changes" and then handle the refresh in the change event entirely.

[JohnnyBGood]

Great!

I thought I had done that, but I only did it on one of the 5 parameters...d'oh!

Thanks guys.