Progress Bar

[Hans810]

Hi All,

During VBA calculations I would like to show a progress bar, so the users can see that the system is busy.
Who can help me with the VBA for this.

Thanks in advance and have a nice Sunday

Regards from Holland

 

[Ruddles]

In brief:-

• when you start your VBA calculations, set the minimum and maximum values of the progress bar to some variable which your calculation can measure; set the value of the progress bar to the minimum value; make the progress bar visible
• as the variable changes, set the value of the progress bar to the value of the variable
• when your calculations end, make the progress bar invisible again

Of course this implies that you know in advance how many loops your code is going to make, how many records it's going to read, etc - otherwise you can't know in advance when you've completed 1%, 2%, etc.

Example:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Public Sub DemoProgressBar()[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  Dim iLoop As Integer[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  With ThisWorkbook.Sheets("Sheet1")
    .ProgressBar1.Value = 0
    .ProgressBar1.Min = 0
    .ProgressBar1.Max = 100[/FONT]
[FONT=Fixedsys]    .ProgressBar1.Visible = True
  End With
   
  For iLoop = 1 To 100    
[COLOR=green]    ' do some processing[/COLOR][/FONT]
[FONT=Fixedsys]    With ThisWorkbook.Sheets("Sheet1")
      .ProgressBar1 = .ProgressBar1.Value + 1[/FONT]
[FONT=Fixedsys]    [COLOR=green]  ' or
[/COLOR]      .ProgressBar1 = iLoop[/FONT]
[FONT=Fixedsys]    End With
  Next iLoop
  
  MsgBox "Done!"
  
  With ThisWorkbook.Sheets("Sheet1")
    .ProgressBar1.Visible = False
  End With
  
End Sub[/FONT]

 

[Hans810]

Hi Ruddles,

Thanks for the response.
Can you tell me where to put the code you sent me?
I tried to put it in a loop as a separate module, but that does work.
I get error message 438 (property or mrthodnot supported by this object)

 

[Maheshp]

Hi
check whether this thread help
www.mrexcel.com/forum/showthread.php?t=565072

 

[Hans810]

Thx Mahesh.

I will try it and let you know.

Regards and have a nice day!
Hans

 

[Ruddles]

Can you tell me where to put the code you sent me?

It's only an example. Place it in a standard code module in a new workbook if you want to test it but you'll need to put something in where it says "do some processing" to slow it down so it's visible - try Application.Wait Now() + TimeValue("00:00:01").

You have to create the ActiveX progress bar on the Sheet1 before you run the code.

If you read through the code you should be able to understand what each statement does. Get it working on its own first, then you can apply the principles to your own workbook.

 

[Hans810]

Hi Mahesh.

It works!!!

Thanks and best regards

Hans