Show userform from another userform

[cplindem]

I am showing a userform with some settings in it to control the main portion of my code. This works properly. I wish to add a 2nd userform that will show a progress bar as the code runs. The problem is that this userform refuses to appear until the code is complete. It also is having trouble unloading.

First I simply show my menu:

Sub Format(control As IRibbonControl)
    FormattingOptions.Show
End Sub

There is a Private Sub UserForm_Initialize(), but it basically just sets some values to combo boxes etc. Also in this menu, there is a button that calls:

Private Sub cmdFormat_Click()

    '~~code that sets some public variables

    Unload Me
    Progress.Show 'userform does not appear until its initialize code has completed for some reason
    'Unload Progress 'does not unload - causes "error 1004: that name is already taken"


End Sub

The Progress userform is intended to show an updating progress bar. But it refuses to appear at this point. It DOES however properly run the simple initialization code:

Private Sub UserForm_Initialize()
    Call MainFormat
End Sub

The idea is that my MainFormat code will run and continuously update the progress bar. Then the Progress userform should close.

Public Sub MainFormat()
    '~~a lot of code that manipulates some data
    'Unload Progress 'causes error 91 on Progress.Show within Sub cmdFormat_Click()
End Sub

At this point the Progress userform finally appears. I don't understand why it doesn't appear when I first show it.
Also, as you may have noticed from my commented lines above, I have also tried to specify where the Progress userform should unload. Both location produce an error.

Any ideas?

[Leith Ross]

Hello cplindem,

By default, UserForms are displayed modally. A modal form disables input to it's owner window (in this case Excel) and the owner's child windows until the UserForm is dismissed.

However, you can change the modallity of the UserForm either by changing the ShowModal property in the Properties Window to False or explicit setting the Show method Modal argument to False at run-time.

Example of Changing the UserForm's Modality at Run-time

    UserForm2.Show Modal:=False

[snb]

But why don't you add the progressbar (that will slow down your code) in the first userform itself ?

[Aflatoon]

Or at least put the formatting code in its Activate event. Never a good idea to unload a form before it finishes loading.

[cplindem]

Thanks for all the assistance.

I tried adding Modal:=False, but the form still did not appear. However, I do see the purpose.

As for snb's suggestion to simply create the progress bar in the original form - that's a good idea. The only issue is that one of the setting options in the form actually opens yet another form with additional settings, before continuing on with the remaining code. So if the user selects that option, I guess I would have to build a progress bar in that form as well. Obviously, only having one instance of the progress bar is the way to go.

However, that got me thinking more about why I am able to successfully get that additional from to appear. It gets shown near the beginning of Sub MainFormat() so I went ahead and added the Progress form directly after that. That worked. Adding Modal:=False produced an error though - error 446: Object doesn't support named arguments. I tried changing the ShowModal property to false and that did work. Does vbModeless do the same thing?

Now I just have to build the progress bar itself and get it to update. I should be able to handle that.

As for Aflatoon's suggestion, I actually had all the formatting code in the form's Initialize event (I'm assuming Activate wouldn't be any different in this case), but I moved it to a module when I began my progress bar journey. I was under the impression that this was better anyway - am I wrong? Also, wouldn't that mean that the Progress form gets shown while the FormattingOptions form is still open? I'm not quite sure what you mean by unloading a form before it finishes loading. Where am I doing that?

Thanks again.

[Aflatoon]

Why would you assume the opposite of what I said? Did you try moving it to the activate event?

[cplindem]

By opposite, do you mean removing the formatting code from the initialize event? If so, I did that before I even started this thread.

I have actually gotten the Progress form to show up at the proper time, but it only appears white. The bar updates as I have coded it to, but it only displays those updates once the code has finished. I am using Progress.Repaint to try to update it at various points in the formatting code, but it refuses to show anything but white. I'm guessing this is why I need to have the formatting code in the Progress form's activate event?

[Aflatoon]

The Initialize event occurs when the form is loaded into memory and before it is shown on screen. The activate event occurs when it is actually shown.

[cplindem]

I have moved the majority of my code inside Progress.Activate() and using Repaint now works properly.

Many thanks