Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Execute Code at Window Restore (97 SR2)

    What sub header to I need to use to execute code when the window is restored? I was thinking workbook_activate() but that's not doing it for me..

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    You might try Window Activate or Window Resize.
    Legare Coleman

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    This doesn't seem to work, any other ideas?

    Private Sub Workbook_WindowResize(ByVal Wn As Excel.Window)
    'code here.
    End Sub
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    Are you rezsizing the main Excel window, or just the activeworkbook window. The Window resize event will only occur in the latter case.

    I think a class module is called for if you want to work with application events.

    Andrew C

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    Any suggestions? I'm not sure where i'd start on a class module...

    I'm minimizing the application window, so that the user can access other programs more efficiently, i'd like to open the form when the application is restored, but can't seem to nail it down...

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    Drk,

    Can I make the following assumptions ?

    1. You are using Excel 97
    2. You have a userform in operation
    3. You want to suspend the userform & minimise Excel
    4. You then want your Userform to re-appear when Excel is restored.

    If so do you propose to have an option on your userform to carry out step 3 ?

    Andrew

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    Yep on all counts.

    I am using the following to capture code upon closure:

    Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
    Cancel = True
    Call Closed
    End If
    End Sub
    Sub Closed()
    System.Range("H" & 1).Value = "Restore"
    Unload Me
    Application.WindowState = xlMinimized
    End Sub

    then I'll do this when the window is restored:

    Sub However this is done...
    If system.Range("H" & 1).value = "Restore" then
    frmtracking.show
    system.range("H" & 1).value = ""
    end if
    end sub

    only problem I have is trying to capture the form's 'restore' to use it as an event to start code...
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    Would your users mind, having restored Excel, taking the trouble to maximise the workbook window. That approach would make it easy to code. I am not sure if there is a quick way to trap the application window events. I attach a workbook with a suspend button, though the same approach could be used in the QueryClose event.

    Let me know if it will do
    Attached Files Attached Files

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    I had hoped for a method which would require no user interaction. I've already got the form re-appearing after they hit CTRL+T, but an alternative to this would be ideal...

    How hard would it be to capture the application event? How might I get started on doing just that? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Thanks for all your help Andrew!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  10. #10
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Execute Code at Window Restore (97 SR2)

    DrkRealm

    Very Interesting !!!

    Ok so we need something that would trigger when the application window is maximized/minimized. There are some APIs that tell us Window State, but they do not trigger when the Window is changed.

    You can apply some Window and workbook events, but that does not help since the change of the Application Window does not affect the Workbook window?!

    Maybe in Excel version 15...

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    Ok Drk,

    Try this one - no class modules, no events - Just an API call <pre>Private Declare Function FindWindow Lib "user32" _
    Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

    Private Declare Function EnableWindow Lib "user32" _
    (ByVal hwnd As Long, ByVal fEnable As Long) As Long

    Private Sub CommandButton1_Click()
    EnableWindow FindWindow("XLMAIN", Application.Caption), 1
    Application.WindowState = xlMinimized
    End Sub</pre>

    Sample workbook attached

    Andrew
    Attached Files Attached Files

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    Works like a charm, Very Impressive!

    Thanks Andrew!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  13. #13
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    Drk,

    If you are going to use it needs tidying up a little. It is in effect turning your userform modeless, which means that users can enter data in the sheet whislt the userform is open. How many worksheets need to be visible, and is it ok to access them while the userform is in operation ?

    Andrew

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    There's no reason for the user to access the main sheet, it's just a plain spreadsheet with columns A - AZ populated. (too scattered to gain anything) Another good thing here, these are users in the simplest of terms. Any attempt to access anything but the userform would be too confusing for them.

    I will resize my application window to match the dimensions of the main form, this should prevent an accidental click into the spreadsheet.

    What an incredible fix, also making the form modeless!!! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>!!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  15. #15
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Execute Code at Window Restore (97 SR2)

    Drk,

    Just an update. The attached form does prevent the selection of data on the sheet whilst the form os displayed. Also the application (Excel) "x" button is disabled to avoid accidental closing of you workbook.. Module1 contains the code to start everthing. (button on sheet).

    Andrew
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •