use a progress bar without a loop

[Zygoid]

I have a userform with 3 frames and 23 check boxes. depending on which checkboxes are used in which frames depends on which modules it will run. can run upto 36 modules. from what i read and found through searching codes I assume progress bars are used only for loops. is this true?

 

[Gavona]

Doesn't have to be. Personally I use the Status bar to give an indication of progress.

Application.StatusBar = "Doing process1"
Some stuff 
Application.StatusBar = "Doing process2"
Some more stuff 
For Each c In Selection
Application.StatusBar = "Applying Formulae to " + c.Value
Some stuff
Next c

'Tidy up at end
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.StatusBar = ""
End Sub

If you want a fancy progress bar then no reason why you could not call your progress bar procedure from different points in your code, sending it info on how much to increment progress by....



Gavin

 

[Zygoid]

thanks for the info gavin.

the progress bar i created from microsoft support page is just coming up with a whited out form. I see what your saying in your code, I could put the application.statusbar="doing process1, 2 ..."at the top of each run "module name" in my coding.

is there a place you know of that I could read in more detail about this?

 

[Gavona]

Search on this forum for progressbar
There are links to other sites, for example, thread707-1034951

Personally I only use status bar. It is simple to understand. In one application I made a little sub that not only updated the status bar but also recorded the status bar text and time elapsed on a worksheet. Useful for figuring what bits were taking the time.

Gavin

 

[Gavona]

This is the code I use. Within each of my subs I call the StatusMonitor routine at various points in order to update the status bar text. If the macro crashes out I run the ResetStatusBar routine to clean things up and get it displaying normally.

Dim Start, Finish, TotalTime, ProgressRow As Integer, StatusText As String
Sub DoAllFour()
Start = Timer
ProgressRow = 1
StatusMonitor ("Hello! Starting to do my stuff!")

Call ApplyExcludes
Call ApplyBasis
Call ApportionCEC
Call ResetStatusBar
Finish = Timer
TotalTime = Finish - Start
MsgBox "Routine took " & TotalTime / 60 & "  minutes"
End Sub

Sub ResetStatusBar()
Application.StatusBar = ""
Application.StatusBar = False
Application.DisplayStatusBar = True
End Sub

Sub StatusMonitor(StatusText)
'Displays text in status bar and records progress in progress sheet
Application.StatusBar = StatusText & "        " & Round((Timer - Start) / 60, 2) & "  minutes so far"
ProgressRow = ProgressRow + 1
Range("Progress").Offset(ProgressRow, 0) = Application.StatusBar
Range("Progress").Offset(ProgressRow, 1) = Round((Timer - Start) / 60, 2)
End Sub

Gavin

 

[Zygoid]

Thanks gavin!