Hi Everyone

I am trying to put some code into the procedure below that will display a message box telling the user that the Query has been completed.

The code below refreshes the data from database using MS Query and enters into the first sheet of the workbook.

I understand that I can use the AfterRefresh event from the query object. I have tried to implement this but with no success.

I'll be glad for any help with this matter.

Private Sub UpdateButton_Click()

ActiveWorkbook.RefreshAll

MsgBox "Update complete"

End Sub

I Have Now resolved the above problem here is the code and where to place it.

This code is entered on the sheet module where the Update button has been placed
Private Sub UpdateButton_Click()

'worksheet initialized the BeforeRefresh event
Module1.Initialize_It
'worksheet initialized the AfterRefresh event
Module2.Initialize_It

ActiveWorkbook.RefreshAll

End Sub

Module1 (Macro module)
Dim X As New Class1
Sub Initialize_It()
Set X.qt = ThisWorkbook.Sheets("Live Data").QueryTables(1)
End Sub


Module2 (Macro module)
Dim X As New Class2
Sub Initialize_It()
Set X.qt = ThisWorkbook.Sheets("Live Data").QueryTables(1)
End Sub


Class1 (Class module)
Public WithEvents qt As QueryTable
Private Sub qt_BeforeRefresh(Cancel As Boolean)
Dim a As Integer
Dim my_Prompt As String
my_Prompt = "Please wait while data refreshes"
a = MsgBox("Do you want to refresh the data now?", vbYesNoCancel)
If a = vbNo Then
my_Prompt = "Data will not be refreshed."
Cancel = True
End If
MsgBox my_Prompt
End Sub

Class2 (Class module)
Public WithEvents qt As QueryTable
Private Sub qt_AfterRefresh(ByVal Success As Boolean)
MsgBox "Data has been refreshed"
End Sub