Need Help with Userform Progress Bar

[brusk]

I've been using a MS Common Controls 6.0 Progress bar in a userform while another userform builds its controls and reads data from Excel tables to populate them. This has worked for the past few years but IT is starting to push 64bit Excel which I've just learned does not support the 32 bit controls. I've tried a few standard things listed like widening a label that has a colored background but the userform doesn't constantly work and seems to lockup until the work being done is finished. Is there a better, reliable method of doing a Progress bar that works with 32 and 64bit.

[snb]

Yes: improve your code so it will run faster: remove all 'selects' and 'activate'; avoid interaction with the worksheet to a minimum (reading once, once writing the results), avoid realtime controlbuilding, use as much arrays as possible, avoid 'additem' to populate listboxes/comboboxes but use .List.

Even the introduction of a progressbar hinders the progress of your code.

[brusk]

Thanks for the advice unfortunately excel isn't the best application when it comes to performance but that was the requirement I was given. The form I'm using doesn't interact with any of the visible sheets just several SQL queries to a hidden sheet of gathering data to populate the several hundred text boxes in a multi framed page. I played with different variations of pulling the data and there wasn't enough of a difference in performance to justify one over the other so I went with using ADODB SQL queries where I could get the exact data I needed over pulling in all of the data into memory and then sorting through it. The form only take up to about 15-20 seconds to load on some of the larger data scenarios but just enough where my end users complained that they didn't know if there was a problem.

[Paul_Hossler]

Googling finds many Excel VBA progress indicators

For example:


http://www.excel-easy.com/vba/exampl...indicator.html

[brusk]

Googling finds many Excel VBA progress indicators

For example:


http://www.excel-easy.com/vba/exampl...indicator.html

Unfortunately that was the first method I tried. It's just changing the width of a label with a colored background. Is what happens is the userform starts and for about 20% through the bar works and then when excel gets to the loops that are doing the most work that userform stops responding until the other form initializes. For some reason changing the label width would not work but the Common Controls built in Progress bar worked flawlessly.

[Paul_Hossler]

Did you UserForm1.Show (vbModeLess) ?

[brusk]

Did you UserForm1.Show (vbModeLess) ?

Yes without the vbModeLess it doesn't do anything and forces the user to close that form. I recreated it and retested and seems like the Form Caption gets updated but the label does not change and you see the number on the top increase but no bar that moves.

Private Sub UpdateProgress(Percent As Integer)    If Percent < 100 Then frmProgressBar.Bar1.Width = Percent * 2
    frmProgressBar.Caption = Percent & "% Complete"
End Sub

[Paul_Hossler]

I'd add a .Repaint and a DoEvents

I'd also store Format(Percent, "0#%") to see if I needed to do anything since I wouldn't want to update for each of 10,000 records only when the % changes (i.e. 100 times), to avoid flicker, etc.

Dim PrevPercent as string


Private Sub UpdateProgress(Percent As Integer)

    If Format(Percent, "0#%") = PrevPercent then Exit Sub


    If Percent < 100 Then
       frmProgressBar.Bar1.Width = Percent * 2 
       frmProgressBar.Caption = Percent & "% Complete" 

       frmProgressBar.Repaint
       DoEvents
    Endif

End Sub

If it'll help, this is an extract of the PB I use. I put it into a class (mostly because I wanted to) but it might give you ideas

[brusk]

You the man. The .Repaint is the part that I needed. Works good now.