loop until A1 = A5

Posted on 2009-12-30
Last Modified: 2013-11-25
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
Question by:karinos57
    LVL 13

    Expert Comment

    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
        Application.Cursor = xlDefault

    Author Comment

    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.
    LVL 19

    Accepted Solution

    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.
    This url describes how to use the AfterRefresh event.
    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
            ' 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

    LVL 32

    Assisted Solution

    by:Robberbaron (robr)
    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


    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now