Progress Bar

[MarkCBB]

Hello,


I have a VBA code that calculates random numbers for about 5 seconds, it it possible to add a Progress bar to the excel sheet?

If so where could I find the control?

 

[Ruddles]

Developer > Insert > ActiveX Controls > More Controls > Microsoft Progress Bar.

You need to work out how many increments your code will produce, so for example if your loop goes round 1000 times you set the bar's Min to 0, Max to 1000 and Value to 0 before the loop starts and increment it by one every time the loop goes round. If your loop goes round numRows times, you'd set the bar's Max to numRows.

Post your code if you get stuck.

 

[MarkCBB]

Hi there Ruddles,

Yes, if you can help I tried a few things but didnt get it right. Here is the code that is running.

Sub Winners5()
Range("B2").Select
Application.DisplayFormulaBar = False
ActiveWindow.DisplayHeadings = False
If Range("F1").Value = "!@#" Then
If WorksheetFunction.Count(ActiveCell.EntireColumn) < 2 Then
Do
ActiveCell.Formula = "=INDEX(OFFSET(Sheet2!$B$1,1,0,COUNTA(Sheet2!B:B)),RANDBETWEEN(1,COUNTA(Sheet2!B:B)-1))"
Do While WorksheetFunction.CountIf(ActiveCell.EntireColumn, ActiveCell.Value) > 1

Loop
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
Calculate
ActiveCell.Copy
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
MsgBox ("Done!")
Range("F1").ClearContents
Else
MsgBox ("Not Allowed!")
End If
Else
MsgBox ("Not Allowed!")
End If
End Sub

 

[Ruddles]

First of all make sure the code is working before you try to add the progress bar. Is your code running okay - looping the correct number of times and producing the correct results?

I can't actually work out what it's doing. Which is your main loop and how is it controlled, i.e. what determines when it terminates?

You should end up with something like this:-

ProgressBar.Min=0
ProgressBar.Max=35 ' (or however many)
ProgressBar.Value=0
 
Do
  ' do something
   ProgressBar.Value=ProgressBar.Value+1
Loop 35 times ' (or however many)