Unload UserForms

[Craig]

Hi,

I am looking for a way to unload UserForms. I can unload one, but need all
others to unload if vbYes is selected:

Code used is:

Private Sub CommandButton1_Click()
Range("A550", Range("A550").End(xlDown)).sort Range("B50"), xlAscending

Dim Res As VbMsgBoxResult
Res = MsgBox("This will sort data into Red, Amber & Green", vbOKCancel)
If Res = vbOK Then
Unload UserForm5
End If

End Sub

UserForm1 is selected prior to UserForm5 as sort is an option on UF5

Any help gratefully received

Many Thanks

Craig

 

[JLGWhiz]

I assume you meant if vbOK is selected since the MsgBox has vbOKCancel.
Give this a try. Post back if there is a problem.

Private Sub CommandButton1_Click()
Range("A550", Range("A550").End(xlDown)).sort Range("B50"), xlAscending

Dim Res As VbMsgBoxResult
Res = MsgBox("This will sort data into Red, Amber & Green", vbOKCancel)
If Res = vbOK Then
For i = 1 To UserForms.Count
Unload UserForm(i)
Next
End If

End Sub

 

[JLGWhiz]

Might work better this way.

Dim Res As VbMsgBoxResult
Res = MsgBox("This will sort data into Red, Amber & Green", vbOKCancel)
If Res = vbOK Then
For i = UserForms.Count To 1 Step -1
Unload UserForm(i)
Next
End If

End Sub

 

[JLGWhiz]

I am having one of those nights:

Dim Res As VbMsgBoxResult
Res = MsgBox("This will sort data into Red, Amber & Green", vbOKCancel)
If Res = vbOK Then
For i = UserForms.Count To 1 Step -1
Unload UserForms(i)
Next
End If

This corrects the syntax for a collection item.

 

[Craig]

My apologies for tardiness....had 'man flu' only just recovered.......

am getting error at: Unload UserForms(i) point in code that you posted

Any ideas?

Craig

 

[JLGWhiz]

Try changing this line:

For i = UserForms.Count To 1 Step -1

To this:

For i = UserForms.Couint - 1 To 0 Step = -1

 

[JLGWhiz]

Just a note that your UserForms must be loaded as ShowModal = False for the
code to work.

 

[Craig]

Sorry, dont get that bit:

To show UF1:

Private Sub CommandButton6_Click()
UserForm5.Show
End Sub

Then press button for sort options and code behind that is:

Private Sub CommandButton3_Click()

Worksheets("Issues").Visible = True
Worksheets("Issues").Select
Range("A1").Activate

Range("A550", Range("A550").End(xlDown)).sort Range("B50"), xlAscending

Dim Res As VbMsgBoxResult
Res = MsgBox("This will sort data into Red, Amber & Green", vbOKCancel)
If Res = vbOK Then
For i = UserForms.Count - 1 To 0 Step = -1
Unload UserForms(i)
Next
End If
End Sub

Dont understand where to put the Modal bit?

Sorry, dont think Man Flu completely gone !!

 

[JLGWhiz]

Maybe I have misunderstood your original post. I thought that you had more
than one userform opened at once and wanted to know how to unload them in
one command. If you only wanted to unload one UserForm then you do not need
the For ... Next loop statement, You can just:

Unload UserForm5 ' or whaterver name it is

To explain the Modal part. The default condition to load a UserForm is
Modal. This condition stops all other events except those controlled by the
UserForm until the UserForm is unloaded. In this condition, only one
UserForm can be loaded at a time. To load more than one UserForm, the
UserForm#.Show ShowModal:=False must be used to load the form in what is
commonly referred to as a modeless condition. More than one UserForm can be
loaded at a time using this method. It was the second method that I had
though you were using, but apparently not. My error, I should have asked
long ago.

 

[Craig]

No you were right......the original post was correct, several forms open and
to be closed altogether.

Iknew where the ShowModal was but couldn't see for looking: have now applied
the ShowModal=False and works perfectly.

Next time I post anything, I will look before I ask, however your
explanation on how it is applied is excellent, thanks

Still learning and very grateful to you for time (and patience) on this.

regards

Craig