How to handle modal UserForms?

Question

I have a UserForm with ShowModal set True. I want to use the dialog as a form and as a progress bar, so after the user clicks "OK", the form should remain on-screen but code should continue to execute in the background. What's the best way to go about doing this?

Currently, I have two forms, one where the user enters data, and then a second with the progress bar. I'd prefer to merge these into one form for simplicity

Answer 1

One way would be to add a progress bar control to your main form. (To do this in Excel, from the form editor, right click on your tool box an select addition controls. Then select Microsoft Progress Control 6.0 (SP6). After it's selected you can add it just like any other control.)

From there you just call the code from user form in question, which will keep the form up. Then if you want to update the progress you can just pass the control to the sub (there are other ways, this is just the lazy way).

Private Sub CommandButton1_Click()
    Module1.MyCode Me.ProgressBar1
End Sub

Public Sub MyCode(ByRef progress As MSComctlLib.ProgressBar)
    'Do stuff
    progress.value = progress.value + 10
    'Do more stuff
    progress.value = progress.value + 50 'It was a lot of stuff
    'And so on.
End Sub