[ExcelVBA] Re: I'd like a Before_UserForm_Terminate Event

Hi Derek,

I can try removing the repaint, but
1 - The form will repeatedly reload in the same session immediately after dismissing it by any of the available means (two buttons and the [x] box). (I interpret the form's re-appearance when I request it to reappear, as reloading)

B - I thought I needed the Repaint to show the label (with the new property of Visible). Been a while since I wrote that code and I am just picking up my "working" code for use elsewhere.

iii - MSDN says (as I quoted) that the Terminate event occurs AFTER the form is unloaded and all elements are set to NULL. I interpret that as meaning that none of the elements of the form exist and, therefore, I can not make the (nonexistent) message (label) visible.
This is what leads me to conclude that I need the equivalent of a BeforeTerminate Event. This is somewhat puzzling since the form is still on the screen as I change the Label property to visible and back to invisible. I understand the difference between the form being left on the screen and eliminating the form's data from memory.

I know enough to be dangerous...

73, Steve, K9DCI

--- In ExcelVBA@yahoogroups.com, Derek Turner <g4swy@...> wrote:
>
> Dear Steve
>
> I did not have time to look at this in any detail but it seems to me that if you are doing Me.Repaint inside the Terminate then you are preventing the removal of, or even reloading the form.
>
> I seem to remember using QueryClose to solve problems like this. Put a debug inside each close event and note the firing sequence.
>
>
> Regards
>
> Derek Turner
> +++
>
>
>
> >________________________________
> > From: noskosteve <noskosteve@...>
> >To: ExcelVBA@yahoogroups.com
> >Sent: Wednesday, 19 June 2013, 3:31
> >Subject: [ExcelVBA] I'd like a Before_UserForm_Terminate Event
> >
> >
> >
> > 
> >Excel 2007
> >
> >Unless one of you has a work around, I'll just move on.
> >
> >I have a UserForm (Name is "PortSetup") that asks for some setup items (ports, baud rates & stuff). It opens with the previously used values.
> >
> >When the Save button is clicked I show a short verification message ("Changes Accepted" - this is a Label I make visible).
> >
> >When the Cancel button is clicked I show another Label on the Form ("Changes Canceled" - Label Name is ChangesCancel).
> >
> >The above all work fine.
> >
> >I would also like to show the Cancel verification message when the [x] Terminate box is clicked, but it won't show (visible = True) when I have the code located in the UserForm_Terminate() Subroutine. The form still is showing, and other things happen, but not the Label Visible...(Google is your friend: see MSDN below)
> >
> >The UserForm_Deactivate() doesn't fire either.
> >
> >MSDN says: The Terminate Event "Occurs when all references to an instance of an object are removed from memory by setting all variables that refer to the object to Nothing..." AND...
> >"The Terminate event occurs after the object is unloaded. "
> >
> >... AFTER...bummer...
> >
> >Pinging the Object Browser for "Before" shows no BeforeTerminate.
> >
> >Looks like this is a nice idea only... Eh?
> >
> >Regards, Steve
> >
> >Code, FWIW:
> >This works:
> >------------- The working Cancel Button Event -----------------
> >Private Sub SetupCancel_Click()
> >Debug.Print "****** Setup Form Canceled ******"
> >' Show verification then hide it.
> >Me.ChangesCancel.Visible = True ' Show it
> >Me.Repaint
> >Wait (40) ' Wait a bit
> >Me.ChangesCancel.Visible = False ' Kill it
> >Me.Repaint
> >Wait (5)
> >
> >ActiveCell.Activate
> >PortSetup.Hide ' Back to Kansas
> >End Sub
> >----- Terminate [x] DOES NOT work (as expected, I guess...)-----
> >Private Sub UserForm_Terminate()
> >Debug.Print "****** Setup Form Terminated ******"
> >' The above line shows in the Immediate Window when [x] is clicked.
> >
> >Me.ChangesCancel.Visible = True ' This doesn't show on the Form!
> >Me.Repaint
> >Wait (40) ' This Wait Sub executes.
> >Me.ChangesCancel.Visible = False
> >Me.Repaint
> >Wait (5) ' This Wait Sub also Executes.
> >
> >ActiveCell.Activate
> >PortSetup.Hide
> >End Sub
> >-------------------------------------
> >[EOF]
> >
> >
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>




------------------------------------

----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ExcelVBA/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/ExcelVBA/join
(Yahoo! ID required)

<*> To change settings via email:
ExcelVBA-digest@yahoogroups.com
ExcelVBA-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
ExcelVBA-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/