Kamis, 20 Juni 2013

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

 



Thanks,

A lot of work just for a user convenience...
It'll take a while to digest.
Regards, Steve

--- In ExcelVBA@yahoogroups.com, Tim Lewis <tlewis@...> wrote:
>
> One option is to remove the default title bar and create your own. You can control it much better that way. Here is an example:
>
> The setup: Add two images
> imgTitle - this will be replica of a title bar.
> imgExit - this will be replica of the X
>
> The longest part will be creating the images for the title and the X. There are couple of ways to do this:
> Option one
> Run your code and go to the part of code that displays your form
> With the form as the active window, press Alt and PrintScreen(may be PrtSc or something else on your keyboard). This copies the image of the active window to the clipboard. Just as a note, if you press PrintScreen alone it copies the entire desktop image to the clipboard. This is useful for when you want to make screenshots of certain menus.
> Open Paint. Change the default properties of the picture size to 5 pixels by 5 pixels. This will make the white part extremely small, but it is needed in pasting.
> Close paint and reopen it. It should now have the 5 x 5 pixel as the default.
> Paste the image from the screenshot/clipboard.
> Open a second instance of paint.
> From Paint instance 1, mark and copy the title bar without the X. Paste this to Paint instance 2. It should be just the title bar without the X. Save the image as a bmp. Close Paint instance 2.
> From Paint instance 1, mark and copy the X. Open a new instance of paint. Paste the X to it that was just copied from instance one. Save the image as a bmp.
>
> Now for the form.
> The picture for imgTitle will be the title bmp that was saved above.
> The picture for imgExit will be the X bmp that was saved above.
>
> Now on the form, move imgTitle and imgExit to the top of the window, just below the real title bar. The real title bar will removed through code, and the images will appear at the top as a "fake" title bar.
> Double click on imgExit to add code to it. Be sure to add the .Hide as the last command, because this image now controls the fate of the form.
>
> Here is an example that I created to test this:
> UserForm1 has 2 buttons, a label, and 2 image boxes
> cmdSave
> cmdCancel
> lblMessage
>
> Here is the code:
> '**** Start of API Calls To Remove The UserForm's Title Bar ****
> Private Declare Function FindWindow Lib "user32" _
> Alias "FindWindowA" _
> (ByVal lpClassName As String, _
> ByVal lpWindowName As String) As Long
>
>
> Private Declare Function GetWindowLong Lib "user32" _
> Alias "GetWindowLongA" _
> (ByVal hWnd As Long, _
> ByVal nIndex As Long) As Long
>
>
> Private Declare Function SetWindowLong Lib "user32" _
> Alias "SetWindowLongA" _
> (ByVal hWnd As Long, _
> ByVal nIndex As Long, _
> ByVal dwNewLong As Long) As Long
>
>
> Private Declare Function DrawMenuBar Lib "user32" _
> (ByVal hWnd As Long) As Long
> '**** End of API Calls To Remove The UserForm's Title Bar ****
>
> '**** Start of API Calls To Allow User To Slide UserForm Around The Screen ****
> Private Declare Function SendMessage Lib "user32" _
> Alias "SendMessageA" _
> (ByVal hWnd As Long, _
> ByVal wMsg As Long, _
> ByVal wParam As Long, _
> lParam As Any) As Long
>
>
> Private Declare Function ReleaseCapture Lib "user32" () As Long
>
>
> Private Const WM_NCLBUTTONDOWN = &HA1
> Private Const HTCAPTION = 2
> '**** End of API Calls To Allow User To Slide UserForm Around The Screen ****
>
> Dim hWndForm As Long
>
> Private Sub UserForm_Initialize()
> Dim Style As Long, Menu As Long
> hWndForm = FindWindow("ThunderDFrame", Me.Caption)
> Style = GetWindowLong(hWndForm, &HFFF0)
> Style = Style And Not &HC00000
> SetWindowLong hWndForm, &HFFF0, Style
> DrawMenuBar hWndForm
> End Sub
>
>
> Private Sub cmdCancel_Click()
> lblMessage.Caption = "Cancel"
> UserForm1.Repaint
> CurrentTime = Timer
> FutureTime = CurrentTime + 3
> Do While CurrentTime < FutureTime
> CurrentTime = Timer
> Loop
> lblMessage.Caption = "Waiting"
> End Sub
>
> Private Sub cmdSave_Click()
> lblMessage.Caption = "Saving"
> UserForm1.Repaint
> CurrentTime = Timer
> FutureTime = CurrentTime + 3
> Do While CurrentTime < FutureTime
> CurrentTime = Timer
> Loop
> lblMessage.Caption = "Waiting"
> End Sub
>
> Private Sub imgExit_Click()
> lblMessage.Caption = "Cancel"
> UserForm1.Repaint
> CurrentTime = Timer
> FutureTime = CurrentTime + 3
> Do While CurrentTime < FutureTime
> CurrentTime = Timer
> Loop
> UserForm1.Hide
> End Sub
>
>
>
> ___________________
>
> Thanks to Rick Rothstein for the code to remove the menu bar:
> http://www.excelfox.com/forum/f22/remove-userforms-titlebar-and-frame-539/
>
> Let me know if anything is unclear.
>
> Tim
>
>
>
> -----Original Message-----
> From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of noskosteve
> Sent: Wednesday, June 19, 2013 10:28 PM
> To: ExcelVBA@yahoogroups.com
> Subject: [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
>

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)
Recent Activity:
----------------------------------
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