Bob,
Terrific! Thanks again so very much for all the time and help you've given me on this - it's very much appreciated. And I'll certainly take into account your caution about being careful to avoid not being able to close the form. (I'll do my experimenting on a "Test" file first.)
Many, many thanks,
Paul
----- Bob Phillips wrote: -----
Paul,
As I think you suspected, it is unloaded at this point.
Good news though, there is a QueryClose event that is invoked whenever the
form is unloaded. There are 4 close circumstances, so you can trap them and
react as you see fit. Here is some code
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Select Case CloseMode
Case vbFormControlMenu:
Cancel = True 'The user has chosen the Close command from the
Control menu on the UserForm.
Case vbFormCode:
'The Unload statement is invoked from code.
Case vbAppWindows:
'The current Windows operating environment
session is ending.
Case vbAppTaskManager:
'The Windows Task Manager is closing the
application
End Select
End Sub
In this I have shown all 4 instabces, but the only one that is trated is the
Control Menu close, the X that is, where I cancel the close. So it has no
effect to click the X.
You could action some of the others, but be careful, you might never be able
to close the dang thing.
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Paul Simon said:
my Userform1.Textbox1.SetFocus line in the Userform_Activate event and
changing the code in my Exit button back to Userform1.Hide (instead of
Unload Me) all works perfectly.built-in "x" button at the top right corner of the Userform) instead of my
Exit button, is the Userform being hidden or unloaded? If unloaded, is
there a way to remove the "x" Close button from the Userform?times, I eventually run out of memory. In fact, I even reached a point
where Excel told me it could not save the file. (And exiting Excel does not
regain the memory. You actually have to reboot.)
Thanks very much for great advice, Bob.
Paul
Paul,
Could I advise you to re-consider. Sometimes, often, it is
better to hide a
sheet rather than unload it, thereby removing that load overhead if you
re-load in the same session. You can still achieve your objective using the
Activate event.
Not saying you should, just make sure you are happy which is the
best way
for you.... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)