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

 

WithEvents doesn't produce anything in the Object Browser (Excel 2007). Entering it into the Immediate window produces an error:
"/!\ Compile error: Expected: line number or statement or end of statement".

May take some fiddling to understand.

This is just something that 'would be nice' to have...

Perhaps it "was" an honorable goal.... (;-)
Thanks.
Regards, Steve

--- In ExcelVBA@yahoogroups.com, Green <1z@...> wrote:
>
> Hi Steve,
>
>
> AFAIK Userform modules are a sort of class module and you can add events to them.
>
>
> Check out the WithEvents keyword.
>
>
> I don't know how useful this is so please don't shout at me! hehehe
>
>
> HTH
> Lisa
>
>
>
>
> Sent: Wed, Jun 19, 2013 4:31 am
>
> 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]
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (7)

Recent Activity:

• New Members 2

Visit Your Group

----------------------------------
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

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

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___