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 (5) |
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
----------------------------------
Tidak ada komentar:
Posting Komentar