Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 18th, 2007, 07:16 PM
Registered User
 
Join Date: Feb 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default Displaying a completed message after the query has

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

 
Old February 19th, 2007, 11:13 PM
Registered User
 
Join Date: Feb 2007
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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






Similar Threads
Thread Thread Starter Forum Replies Last Post
Displaying message on the form nasirmunir Javascript How-To 3 June 4th, 2008 10:20 PM
Grid Not Displaying Error Message rojiin ASP.NET 2.0 Basics 0 October 22nd, 2007 05:13 AM
Subreport not displaying no data message in main lic023 Access 0 March 15th, 2005 08:15 AM
displaying the query lanita PHP How-To 6 August 23rd, 2004 11:11 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.