User Form Close Event

[Microsoft]

Word 2000

I have a user form that needs to run a couple of lines of code each time
it gets closed. I have the following in the click-event of my Close
button:

Private Sub cmdClose_Click()

Close FileNumber
Unload ufErrorLookup

End Sub

The problem is that this code does not run if the user clicks the "x" in
the upper-right corner of the form. Clicking the "x" probably unloads
the user form, so I'm not really concerned about that, but I am concerned
about running the Close FileNumber statement to close an open random
access file. The file needs to stay open until the user form is closed.

I know that it's possible to write Initialize event macros that will run
after a form has been loaded, but before it's displayed, so I figured
that it must also be possible to write a Close event macro that would run
every time the user form is closed--no matter how it gets closed.
However, I've been unable to find anything in Help on how to do this.

Currently, the only way I can think of to make sure the Close FileNumber
command runs is to add the following routine to my project:

Sub LoadUserForm()

ufErrorLookup.Show
Close FileNumber

End Sub

This would display the user form, and close the file after the user form
has been closed. I guess that's not a bad way to do it, but I think that
closing the file through the Close event of a form (if such a thing
exists) would be cleaner.

Is there a Close event for user forms? If so, what is the first line (I
tried "Private Sub UserForm_Close()" and "Private Sub UserForm_Exit()")?

--Tom

 

[Jonathan West]

Hi Tom,

You need to make two changes.

1. Add the following routine

Private Sub UserForm_Terminate()

Close FileNumber


End Sub

This clause the file to be closed when the userform is closed, no matter
what method is used to close the form.

2. Change the cmdClose_Click routine as follows

Private Sub cmdClose_Click()

Unload Me

End Sub

The reason to use "Me" rather than the form name is so that the current
running instance of the form is unloaded, rather than the instance that
happens to carry that name.

Also note that closing the file has been taken out of this routine and moved
to the UserForm_Terminate routine.

 

[JB]

Word 2000

I have a user form that needs to run a couple of lines of code each time
it gets closed. I have the following in the click-event of my Close
button:

Private Sub cmdClose_Click()

Close FileNumber
Unload ufErrorLookup

End Sub

The problem is that this code does not run if the user clicks the "x" in
the upper-right corner of the form. Clicking the "x" probably unloads
the user form, so I'm not really concerned about that, but I am concerned
about running the Close FileNumber statement to close an open random
access file. The file needs to stay open until the user form is closed.

I know that it's possible to write Initialize event macros that will run
after a form has been loaded, but before it's displayed, so I figured
that it must also be possible to write a Close event macro that would run
every time the user form is closed--no matter how it gets closed.
However, I've been unable to find anything in Help on how to do this.

Currently, the only way I can think of to make sure the Close FileNumber
command runs is to add the following routine to my project:

Sub LoadUserForm()

ufErrorLookup.Show
Close FileNumber

End Sub

This would display the user form, and close the file after the user form
has been closed. I guess that's not a bad way to do it, but I think that
closing the file through the Close event of a form (if such a thing
exists) would be cleaner.

Is there a Close event for user forms? If so, what is the first line (I
tried "Private Sub UserForm_Close()" and "Private Sub UserForm_Exit()")?

--Tom

HI Tom,

Try this

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
' disable the X cancel button
If CloseMode = vbFormControlMenu Then
Cancel = False 'cancel the close
End If
End Sub

HTH

J

 

[Montana DOJ Help Desk]

JB,

Thanks for the suggestion. I had thought about disabling the "x", but I
couldn't figure out how to do that.

Jonathan also replied as showed me how to use the Terminate event, and that
looks like the way to go for my particular user form.

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."

 

[Montana DOJ Help Desk]

Jonathan,

Thanks for the reply. I had actually seen the Terminate event and even read
the Help file on it, and while it sounded promising, I wasn't sure that it
was what I needed. What can I say, it was 4:30 AM!

I have never heard of Unload Me, and I can't find anything on it in Help. I
understand the point that you are making with that recommendation, but I
don't think that I need to worry about multiple instances of the form. I
try to write my code so that each time a user form is loaded, it gets
unloaded before the user can do anything else.

In this particular case, the user form is displayed only when the user
clicks a button on a custom toolbar--it is not loaded by any other routines.
The user form does just one highly specific thing, and it stays on the
screen until the user is done with the task, at which point there is no
reason to keep it open, so it gets unloaded.

I explain all this only because I have not been able to find any info in
Help regarding Unload Me, so I'm unclear as to whether or not it would
benefit me in this case. I suspect that given the particular circumstances
of how this user form is being used, I'm probably okay with referring to the
user form by name in the Unload command. Please let me know if this is not
correct.

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."

 

[Jonathan West]

The Me keyword is listed in the VBA help file. "Me" refers to the instance
of a class or UserForm containing the code that uses the keyword.

In most cases, code within a form should refer to Me rather than to a named
instance of the form. If you do this, you are protected from the following
potentialproblems

- the code failing if you change the name of the form
- the code getting mixed up if you have multiple instances of the form

In your particular case, where you only use one instance of the form under
one name, then it doesn't matter all that much. But if you get into the
habit of using the Me keyword, then your code becomes that much more robust
if you decide to change the use of a form.

 

[Montana DOJ Help Desk]

Okay, I found the ME keyword in the VBA Help. I generally use the search
function to find stuff, and just didn't think about going to the Contents
and browsing the keywords (Doh!).

-- Tom

State of Montana
Department of Justice Help Desk

"Making the world a safer place."