Unload a Form using SelectionChange

[Casey]

I have a Calendar Form that is opened with a SelectionChange. Is there a way
to Unload it with a SelectionChange event? I assume it would have to be in
the same subroutine. Here is the current code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("H:H,K:K,L:L")) Is Nothing Then
OpenCalendar
End If

End Sub

 

[JLGWhiz]

You could probably use the UserForm Exit event to unload the form.

Private Sub UserForm_Exit()
Unload Me
End Sub

I don't believe it can be incorporated into the SelectionChange.

 

[Casey]

Thanks for the reply, but the UserForm is automatically dismissed when you
select a date or can be manually closed using the close X. What I'm hoping is
that the Form would unload if you selected any other column besides H, K or
L.
--
Casey

You could probably use the UserForm Exit event to unload the form.

Private Sub UserForm_Exit()
Unload Me
End Sub

I don't believe it can be incorporated into the SelectionChange.

 

[JLGWhiz]

If the UserForm is modal, you cannot select another column. The form would
have to be modeless to make the selection, but I am still trying to
visualize how that could be written into the SelectionChange code, and I
can't.

Thanks for the reply, but the UserForm is automatically dismissed when you
select a date or can be manually closed using the close X. What I'm hoping
is
that the Form would unload if you selected any other column besides H, K
or
L.

 

[JLGWhiz]

Well, this worked, but it is not something I would do.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
UserForm1.Show vbModeless
Else
Unload UserForm1
End If
End Sub

Thanks for the reply, but the UserForm is automatically dismissed when you
select a date or can be manually closed using the close X. What I'm hoping
is
that the Form would unload if you selected any other column besides H, K
or
L.

 

[JLGWhiz]

Since you are calling a subroutine to open the calendar form, you would have
to modify that sub to show the form modeless. Then you will need an If
statement in the SelectionChange sub that only allows the Unload UserForm
statement when there is a form to close. Otherwise, you get an error.

Thanks for the reply, but the UserForm is automatically dismissed when you
select a date or can be manually closed using the close X. What I'm hoping
is
that the Form would unload if you selected any other column besides H, K
or
L.