HELP: display status bar on excel userforms

[sam]

Hi All,

I have a userform for students where they input their data, attach their hw,
and submit their hw..

Now I want to show them "Status bar" kind of object where they know how far
they are in their process..

So for eg, If they launch the form, The status bar should should 0% and say
(pleas populate your details), once they fill in their details the status bar
should show 30% and say (please attach your hw), once they attach their hw,
the submit button is enabled.. then the status bar should show 60% and say
(please submit your hw)
Once they click submit the status bar should show 100% and say (Success! Hw
submitted)

Can this be done in excel userform?

Thanks in advance

 

[Patrick Molloy]

a nice way is to have two labels, one on top of the other

the one 'below' will be the frame - so have borders but no interior color
the one 'above' should be sized to fit just inside the one below, but with
no borders a filled with blue.
when the form opens set the 'inner' width to zero
when you want to show say 30%, set the inner width to 30% on the one below

 

[EricG]

Here is an example of how to add a progress bar to a user form. Follow the
steps below.

1. Start with a new workbook for this example.
2. Open the Visual Basic Editor (Tools/Macro/Visual Basic Editor)
3. From the menu, Insert/UserForm (named UserForm1 by default)
4. From the menu, Tools/Additional Controls..., find and check the
"Microsoft ProgressBar Control"
5. Open UserForm1 in form view to edit it.
6. From the Toolbox, select the ProgressBar control. Draw a ProgressBar
control on the userform. Named ProgressBar1 by default.
7. Also on the userform, add a new text label, named Label1 by default.
8. Right-click on UserForm1, select "View Code"
9. Enter the code below. Watch out for line breaks!
10. "Run" the form by clicking the Run button on the Debug tool bar, or
from the Run menu. Click anywhere in UserForm1 (other than ProgressBar1 or
Label1), and the value of the progress bar will advance until it reaches 100.
The text label will also update.


Option Explicit

Private Sub UserForm_Click()
Me.ProgressBar1.Value =
Application.WorksheetFunction.Min(Me.ProgressBar1.Value + 5, 100)
Me.Label1.Caption = Format(Me.ProgressBar1.Value / 100#, "0%")
Me.Repaint
DoEvents
End Sub

This example shows you how to update the value of the progress bar, and how
to use a text label to show the value of the bar as a text string. Adapt the
example to your needs.

HTH,

Eric

 

[sam]

Hey EricG, Thanks for your help.

The status bar is updating when I click on the userform, BUT it doesnt
update the status when i populate the textboxes, dropdowns, option buttons
that i have on the form. Am i missing something here?

Thanks in advance

 

[Patrick Molloy]

controls have a property called 'tag' that isn't used.

You should put a sequence of numbers in each that you want the student to
use, so that they are tagged 1,2,3 .... and so on

then each time a control is entered or left, you call a process that checks
the tagged controls to see how many are yet to be used. This can also be a
way to enable controls.
so set enabled for all the tagged controls to false
set the control with the tag value 1 to true

you can then, in your status bar loop enable the next control.

I have 5 text boxes, tagged 1 to 5 with 2 thro 5 disabled.
changing a value in 1, enables 2. Changing 2 enables 3 ...

Option Explicit
Private Sub TextBox1_Change()
enablenext TextBox1.Tag
End Sub
Private Sub TextBox2_Change()
enablenext TextBox2.Tag
End Sub
Private Sub TextBox3_Change()
enablenext TextBox3.Tag
End Sub
Private Sub TextBox4_Change()
enablenext TextBox4.Tag
End Sub
Sub enablenext(Box As Long)
Dim ctrl As Control
For Each ctrl In Me.Controls
If ctrl.Tag = Box + 1 Then
ctrl.Enabled = True
Exit For
End If
Next
End Sub

 

[sam]

Hey Pat, I tried taging the textboxes but when i update them it still doesnt
update the status bar.. So basically I tagged the textboxes and used this code

Private Sub UserForm_Click()
Me.ProgressBar1.Value =
Application.WorksheetFunction.Min(Me.ProgressBar1.Value + 5, 100)
Me.Label26.Caption = Format(Me.ProgressBar1.Value / 100#, "0%")
Me.Repaint
DoEvents
End Sub

Do I need to write something that would make it happen?

Thanks in advance

 

[sam]

Also, I dont want to use the enable function rite now, maybe for next
release.. But rite now I just want to get this status bar working.