Progress Indicator - Simple!!

[m0atz]

Afternoon all,

I've managed to come up with a very very simple progress indicator, it's that basic I have simply entered values throughout the code for the %'age of progress which gets called into the "updateprogress" procedure.

Anyway, the way I've got it set up is as follows:

User presses button on worksheet, this activates sub start()

sub start()
userform1.labelprogress.width = 0
userform1.show
end sub

private sub userform_activate()
call q1top10
'q1top10 is the name of my macro which is doing the work
end sub

sub q1top10
dim pctdone
'mainly copy and pasting and vlookups etc
'throughout the code i have as follows:
pctdone = 0.33
call updateprogress(pctdone)
'more code
pctdone = 0.66
call updateprogress(pctdone)
'more code
pctdone = 1
call updateprogress(pctdone)
unload userform1

sub updateprogress(pct)
with userform1
.frameprogress.caption = format(pct,"0%")
.labelprogress.width = pct * (.frameprogress.width - 10)
end with
doevents

My question is this however: I have four command buttons on the worksheet for the users to select results from Q1, Q2, Q3 and Q4. The progress bar is just so they know that the request has been made and its being done, however, do I have to make 4 versions of my progress indicator, or can the private sub userform_activate() sub call different macros depending on what "button" has been pressed to run the sub start()?

Many thanks for your help.

Col

 

[ChrisM]

Nope, your code can be identical. Though you will probably want some type of identifier at the top of the user form so the user knows which of the 4 process types are running.

Maybe Userform1.Caption = "ProcessA"

Then make sure any setup or cleanup code you write takes all 4 variations into account.

 

[Joe Was]

The "Select Case" structure is a great way to branch one process to many options. Let your Select Case change the caption or other properties, like colors, to the choice made.

 

[m0atz]

Guys,

thanks for the responses, I was thinking of doing a Select Case structure, however, I'm just a little stuck as to how to do it...

Can anyone give me a quick example?

Much appreciated.

Colin

 

[Joe Was]

Sub myMonthRpt()
'Standard module code, like: Modul1!
Dim myMsg$, myTitle$, myDefault$, myMonth$, mySelected As Variant

myMsg = "Enter a month number" & vbLf & "between 1 and 12"
myTitle = "Run Monthly Report!"
myDefault = "1"

'Display InPutBox: message, title, and default value.
myMonth = InputBox(myMsg, myTitle, myDefault)

If myMonth = "" Then GoTo myEnd

Select Case myMonth

Case "1"
mySelected = MsgBox("Month: " & myMonth & ", January!", vbInformation + vbOKOnly, "You Selected:")

Case "2"
mySelected = MsgBox("Month: " & myMonth & ", February!", vbInformation + vbOKOnly, "You Selected:")

Case "3"
mySelected = MsgBox("Month: " & myMonth & ", March!", vbInformation + vbOKOnly, "You Selected:")

Case "4"
mySelected = MsgBox("Month: " & myMonth & ", April!", vbInformation + vbOKOnly, "You Selected:")

Case "5"
mySelected = MsgBox("Month: " & myMonth & ", May!", vbInformation + vbOKOnly, "You Selected:")

Case "6"
mySelected = MsgBox("Month: " & myMonth & ", June!", vbInformation + vbOKOnly, "You Selected:")

Case "7"
mySelected = MsgBox("Month: " & myMonth & ", July!", vbInformation + vbOKOnly, "You Selected:")

Case "8"
mySelected = MsgBox("Month: " & myMonth & ", August!", vbInformation + vbOKOnly, "You Selected:")

Case "9"
mySelected = MsgBox("Month: " & myMonth & ", September!", vbInformation + vbOKOnly, "You Selected:")

Case "10"
mySelected = MsgBox("Month: " & myMonth & ", October!", vbInformation + vbOKOnly, "You Selected:")

Case "11"
mySelected = MsgBox("Month: " & myMonth & ", November!", vbInformation + vbOKOnly, "You Selected:")

Case "12"
mySelected = MsgBox("Month: " & myMonth & ", December!", vbInformation + vbOKOnly, "You Selected:")

Case Else
MsgBox "Error: You Entered: """ & myMonth & """ you must" & vbLf & _
"enter one month's number: 1 to 12!", vbCritical + vbOKOnly, "InPut Error"
End Select

myEnd:
End Sub

 

[tusharm]

In a way you have created a tail that wags the dog. The architecture should be having the utility feature (progress bar) called from the functional code doing the actually work and not having the progress bar code drive the functional capability you are providing. You might want to look at **** Kusleika's post and my comment in
http://www.dailydoseofexcel.com/archives/2007/02/10/yet-another-progress-bar/

Afternoon all,

I've managed to come up with a very very simple progress indicator, it's that basic I have simply entered values throughout the code for the %'age of progress which gets called into the "updateprogress" procedure.

Anyway, the way I've got it set up is as follows:

User presses button on worksheet, this activates sub start()

sub start()
userform1.labelprogress.width = 0
userform1.show
end sub

private sub userform_activate()
call q1top10
'q1top10 is the name of my macro which is doing the work
end sub

sub q1top10
dim pctdone
'mainly copy and pasting and vlookups etc
'throughout the code i have as follows:
pctdone = 0.33
call updateprogress(pctdone)
'more code
pctdone = 0.66
call updateprogress(pctdone)
'more code
pctdone = 1
call updateprogress(pctdone)
unload userform1

sub updateprogress(pct)
with userform1
.frameprogress.caption = format(pct,"0%")
.labelprogress.width = pct * (.frameprogress.width - 10)
end with
doevents

My question is this however: I have four command buttons on the worksheet for the users to select results from Q1, Q2, Q3 and Q4. The progress bar is just so they know that the request has been made and its being done, however, do I have to make 4 versions of my progress indicator, or can the private sub userform_activate() sub call different macros depending on what "button" has been pressed to run the sub start()?

Many thanks for your help.

Col

 

[m0atz]

Guys

Thanks for this, the article is very helpful...

All the best..

Colin