?
Solved

loop until A1 = A5

Posted on 2009-12-30
4
Medium Priority
?
470 Views
Last Modified: 2013-11-25
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
0
Comment
Question by:karinos57
4 Comments
 
LVL 13

Expert Comment

by:rettiseert
ID: 26147718
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
 

Author Comment

by:karinos57
ID: 26147859
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
 
LVL 19

Accepted Solution

by:
folderol earned 600 total points
ID: 26149154
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
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 400 total points
ID: 26165186
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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

807 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