XL2003 - Create A Macro Execution Progress Checklist with Userform

[TechTank]

Hi,

I have 24 macros that each execute two web queries, pull the data into a sheet, do some fancy manipulation and then put some values into another worksheet to display for me. Each macro calls the next until the last macro.

These executions can take some time to complete and so I was thinking that if, upon execution of the first macro, a UserForm appeared and after each macro had finished it could display a "ticked" check box next a label corresponding to the macro it had finished executing.

For example:

Click Button "Import"
UserForm appears with Labels like:
Apples
Oranges
Pears

The macro for Apples begins it's execution and upon finishing it creates a tick next to the label Apple on the UserForm. Rinse and repeat for Oranges and Pears.

I know you can display a progress bar using a UserForm How to display a progress bar with a user form in Excel but is this too advanced for Excel? Can anyone help me at all with this challenge that my brain has thought of but hasn't a clue where to begin?

Thanks for reading and apologies if it's vague but please feel free to ask for more information.

Mark.

 

[eric.xls]

Something like this?

a. Create an userform
b. Add 23 checkboxes
c. Create a module
d. Insert this code and replace the names with your macros

Sub load_Progress()
UserForm1.Show vbModeless
End Sub

Sub Macro_1()
load_Progress
UserForm1.CheckBox1.Value = True
Macro_2
End Sub

Sub Macro_2()
UserForm1.CheckBox2.Value = True
Macro_3
End Sub

Sub Macro_3()
UserForm1.CheckBox3.Value = True
Macro_4
End Sub

Sub Macro_4()
UserForm1.CheckBox4.Value = True
Macro_5
End Sub

Sub Macro_5()
UserForm1.CheckBox5.Value = True
Macro_6
End Sub

Sub Macro_6()
UserForm1.CheckBox6.Value = True
Macro_7
End Sub

Sub Macro_7()
UserForm1.CheckBox7.Value = True
UserForm1.Hide
End Sub

 

[TechTank]

Something along those lines . I'll give this a whirl and thanks for helping eric,

I thought it might have been a bit vague for people to understand.

 

[eric.xls]

You welcome. Let me know how the whirl goes. This method of having tick boxes to indicated progress remind me to the Uninstall application wizards form windows 3.11.

 

[TechTank]

The whirl went amazingly! Thank you so much. I added your code into my existing macros and expanded on it a little but absolutely brilliant mate!

3.11...nostalgia trip?

 

[rmoreno]

Hello all, just found this thread and was trying to use it in my code. Could not get it to work. When I try to use the above code the blank form comes up, all my macros run and only after the last macro runs does the form update with all the checkboxes checked. Does anyone have any idea what I may be doing wrong?

 

[rmoreno]

Here is the VBA code I have been working with. Maybe someone can help identify why the checkboxes are not visible until the very end of the macro run:

Sub ShowMacroProgress()</SPAN></SPAN>
    frm_DataUpdate.Show vbModeless</SPAN></SPAN>
End Sub</SPAN></SPAN>
 
Sub UpdateData_Step1()</SPAN></SPAN>
    Call ShowMacroProgress</SPAN></SPAN>
    frm_DataUpdate.chk_UpdateHeader.Value = True</SPAN></SPAN>
    Call HeaderSummary_DataCapture</SPAN></SPAN>
    Call UpdateData_Step2</SPAN></SPAN>
End Sub</SPAN></SPAN>
 
Sub UpdateData_Step2()</SPAN></SPAN>
    frm_DataUpdate.chk_UpdateGrade.Value = True</SPAN></SPAN>
    Call GradeDetail_DataCapture</SPAN></SPAN>
    Call UpdateData_Step3</SPAN></SPAN>
End Sub</SPAN></SPAN>
 
Sub UpdateData_Step3()</SPAN></SPAN>
    frm_DataUpdate.chk_UpdatePassFail.Value = True</SPAN></SPAN>
    Call PassFailAnalysis_DataCapture</SPAN></SPAN>
    Call UpdateData_Step4</SPAN></SPAN>
End Sub</SPAN></SPAN>
 
Sub UpdateData_Step4()</SPAN></SPAN>
    frm_DataUpdate.chk_UpdatePassFail.Value = True</SPAN></SPAN>
    frm_DataUpdate.Hide</SPAN></SPAN>
    MsgBox "Data Has Been Updated"</SPAN></SPAN>
End Sub

</SPAN></SPAN>