loop until A1 = A5

Hi
I need somekind of loop whether it is for loop or do while or anykind of other loops or even if statements; i am just not sure what is the best way of doing it- here is my pseudocode:

show application.cursor = xlwait
as long as A1 is not equal A5
once A1 = A5 then i want to display
application.cursor = xldefault

can someone help me with a code in the above pseudocode?  thanks
karinos57Asked:
Who is Participating?
 
folderolConnect With a Mentor Commented:
There are a few choices for handling this situation.  The one I prefer is to create a class so that you can use the querytables refresh event.

http://msdn.microsoft.com/en-us/library/bb210432.aspx
This url describes how to use the AfterRefresh event.

http://msdn.microsoft.com/en-us/library/bb211445.aspx
This url describes how to create the class, necessary before you can trap the event.

I would show a userform with a simple textbox message, using

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Intersect(Target, Range("A1")) Is Nothing) Then
        UserForm1.Show 0
    End If
End Sub

and have the AfterRefresh event close the userform.

Private Sub QueryTable_AfterRefresh(Success As Boolean)
    If Success Then
        unload userform1
    Else
        ' Query failed or was cancelled
    End If
End Sub

A simpler choice is to just change the query so it does not refresh in the background.  This will force the user to wait.

For something in the middle, you can stop triggering the refresh with the change on A1, and instead use the worksheet_change event like this...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not (Intersect(Target, Range("A1")) Is Nothing) Then
        UserForm1.Show 0
        activesheet.listobjects(1).querytables(1).refresh false
        unload userform1
    End If
End Sub

Tom.
0
 
rettiseertCommented:
You can use this:

    Dim A1 As Integer
    Dim A5 As Integer
   
    'Example initial values:
    A1 = 0
    A5 = 100

    Application.Cursor = xlWait
   
    Do While A1 <> A5
        A1 = A1 + 1  'Example
    Loop
   
    Application.Cursor = xlDefault
0
 
karinos57Author Commented:
i don't think i can initialize like that.  this is what i am trying to do, I've got an Excel spreadsheet that pulls data from sql server.  The user puts code number in A1 as soon as that happens then the external data in the sheet is refreshed
Issue: the user has no indication that the program is doing anything.  Sometimes the query completes quickly in 20 seconds.  But sometimes it may take upwards of 2 minutes depending the size of the data, during which time the user has no clue that anything is being updated.  I'd like something that lets the user know that the data is being refreshed and they need to wait.
0
 
Robberbaron (robr)Connect With a Mentor Commented:
As I understand it, the value put into A1 is a code that the query uses to return data.

So I would be using the Worksheet_Change as well like folderol but a little differently.


Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        'cell A1 has changed'
        'query will refresh automatically ???
         UserForm1.Show 0  'show the Please Wait form
     ElseIf Not Intersect(Range("A5"), Target) Is Nothing Then
         'query has loaded new data into A5
         Unload UserForm1
    End If
End Sub

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.