Closing excel userforms

[Jim]

I have a multiworksheet workbook with userforms that are needed sometimes
but not all of the time on different sheets. I have a navigator form that
is always shown and that has checkboxes, each will open/close the
appropriate larger userform when needed. When the larger userform
closes/hides from either clicking the navigator check box or when the
larger userform closes from a command button, everything is fine because I
have it clear the checkbox on the navigator form. The problem arises when
a person closes the larger userforms with the x button on the userform
title bar - then the navigator checkbox isn't cleared and they get out of
synch. I can't find a VBA name that is like On_Unload or something that
will clear the box whenever the userform is hidden or unloaded. I tried
queryclose, but no avail - probably did it wrong.

Thanks for any help that you can offer.

Jim

 

[Henry]

Jim,

Try this in the form code

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub

Henry

 

[Jim]

Henry,

Thanks - that worked. I see it stopped the larger userform from closing
with the x in the first place. Funny thing, when I used the name of the
form (Settings_Form) instead of userform, it didn't work. Is this
because of the underscore between Settings and Form. ?

Again, my thanks.

Jim

 

[Bob Phillips]

Jim,

No it is because it is not referring to the name of the form
(Settings_Form), but the Userform object itself.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Jim]

Bob (and Henry before him)

The light shines upon us mortals.

Again, my thanks


Jim