Progress Bar and Screen Updating Question

[pistonbroke]

Hello all. Sorry - this is a bit drawn out.... I have a large 2 sheet database and a macro that compares matched cell info on each line of each sheet. On finding a match the macro tabulates the findings (departmentises each group). All works great. I also have a progress bar (in smoothed mode) that sets it's max value initially, as the value of the last row in the first sheets data. On each of (5000 +) lines "fmImport_Progress.pb1.Max = PB", additionally as each line is looked up, that value (line number), acts as the progress bar value, and i get a smoothly operating progress bar till the final row is reached (100%). However i would also like to show %complete, which i do with a label, but the label % complete value only shows if i turn on Screen Updating in code, but then the screen of course flashes for the duration and the smooth progress bar gets erratic visually.... How can i make the Label showing % progress, update as the macro runs whilst having Screen Updating turned off in code ? Thanks for any help.

Here's a bit of the code, where fmImport_Progress is the user form, and pb1 is the Progress Bar

"fmImport_Progress.Show
fmImport_Progress.pb1.Max = PB

For R = 1 To PB

Pcent = Format((R / PB), "0.0%")

fmImport_Progress.lbPB1.Caption = Pcent
fmImport_Progress.pb1.Value = R

 

[pistonbroke]

Re: Progress Bar and Screen Updating Question-Update by Author

After more testing, it appears Screen Updating is not the issue. Even with it ON, the label in the form does not update (until the Progress Bar has completed). Any ideas appreciated. Thanks

 

[smozgur]

Hi @pistonbroke,

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

Regarding your question about updating the label caption: You can actually use DoEvents in the loop to make it work smoothly. I am aware it is likely a "late answer" for you but hope this helps future readers

For R = 1 To PB
    DoEvents
    Pcent = Format((R / PB), "0.0%")
    fmImport_Progress.lbPB1.Caption = Pcent
    fmImport_Progress.pb1.Value = R
Next R

 

[pistonbroke]

Thanks smoz, yeah i got the notice from the board asking to mark all my posts with solutions and since this question was 12 years old unanswered i figured i'd put it out of its misery by flagging it as done. 10-4 on the DO Events note