--- David Grugeon wrote:
> Hi Steve.
> Only been half following this one so excuse silly suggestion.
>
> One of the actions the terminate will take is to close the form, so could you do your stuff on the beforeClose event of the form? You may need to either cancel event, then recreate it after you had finished your stuff, or find another way of pausing the terminate until the required label had been seen.
> Regards, David Grugeon
Stream of thoughts follows...
Hmmmm Maybe only half-silly... Remember;
1 - I want to click the [x] and have a confirmation message say "Changes Cancelled", or Discarded". The [x] causes the Terminate Event (as I understand what I see happen), not a BeforeClose Event (hence my Subj).
B - MSDN says all elements of a form have ALREADY been set to NULL BEFORE the Terminate event occurs (before the Terminate Sub is even entered).
I did try RE-"Showing" the form in the Terminate sub thinking it would reinitialize everything in the form, but it hung up somehow and I dropped that.
HOWEVER...
Perhaps, just perhaps, I can reinitialize (re-define) my label/message myself rather than just setting it to visible.... If it is Null (but still there) and I make it visible, I still see nothing(Null). However, If VBA will let me re-define the label inside the Terminate Sub, that may just work...
In other words, something like this:
Private Sub UserForm_Terminate()
Me.Label1.Value = "Changes Canceled"
Wait (40) ' My own wait routine.
End Sub ' Form disappears from screen here.
------
I may have to set the location and other parameters, but this sounds simpler than re-doing the Title Bar and [x]. Worth a try.
Though, having the ability to do my own Title Bar has interesting future possibilities... );-E
BTW:
I just tried entering a: Private Sub Userform_beforeclose()...no deal.
Thanks for the nudge...
I'll report results if any.
Steve
> On 21 June 2013 09:40, noskosteve <noskosteve@...> wrote:
>
> >
> > 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]
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > ----------------------------------
> > 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
> >
> >
> >
> >
>
>
> [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 (9) |
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