Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Minimized Excel and displaying forms (XL2002)

    Greetings.

    Can anyone tell me how I can automatically display forms when Excel is minimized?

    I had previously hidden Excel during form display and execution, as there's nothing to do in Excel itself for the user, but my client doesn't like that. Now I'm minimizing Excel, but when I try and display a form using .SHOW the taskbar flashes at me. Clicking Excel shows the form, but leaves Excel minimized and I can then use all my other forms from there - they display quite happily while leaving Excel alone on the task bar.

    So, how can I show a form (modally) and get it displayed once I have already hidden Excel?

    Thanks.

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

    Re: Minimized Excel and displaying forms (XL2002)

    Try including

    Application.WindowState = xlMinimized

    before using .Show

    Andrew C

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minimized Excel and displaying forms (XL2002)

    Andrew, hi.

    That's what I'm doing already. The taskbar justr flashes at you when the time comes to display the form.

    The attached example illustrates - just run the Macro test - it should minimize Excel and show a simple form with the word "Boo" and a button to close it. It does, but Excel flashes at you from the taskbar, and you only see the form when you click on Excel.

    Regards,

    Stuart
    Attached Files Attached Files

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Minimized Excel and displaying forms (XL2002)

    Stuart, interesting problem. In XL97, it seemed to blink no matter what I tried. Finally got the code below to work in XL97. Let us know if it works in 2002. Note, there are two more "tricks" to try if it dosn't work. (1) Add "DoEvents", (2) Add "AppActivate Application.Caption" HTH --Sam

    In Module1:
    <pre>Sub test()
    Application.Visible = False
    UserForm1.Show
    End Sub</pre>


    In UserForm:
    <pre>Option Explicit

    Private Sub CommandButton1_Click()
    Unload Me
    End Sub

    Private Sub UserForm_Initialize()
    Application.WindowState = xlMinimized
    Application.Visible = True
    End Sub

    Private Sub UserForm_Terminate()
    Application.WindowState = xlMaximized
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minimized Excel and displaying forms (XL2002)

    Well, it worked eventually!

    Setting the application to hidden and then visible had no noticeable effect, and neither did using the DoEvents command (there's a slight curiosity in that it all works perfectly if the VB Editor window is open and in normal or maximised views, regardless of what Excel is doing).

    However, with the VB Editor firmly away, the AppActivate command works perfectly - it simulates what you do with the clicking, but I couldn't find the right method when I was looking earlier, so thanks.

    For those of you who are interested in the "full" solution, the test code now looks like this (file attached)

    In the code module:

    <pre>Sub test()
    Application.Visible = False
    UserForm1.Show
    End Sub
    </pre>


    In UserForm:

    <pre>Option Explicit

    Private Sub CommandButton1_Click()
    Unload Me
    End Sub

    Private Sub UserForm_Initialize()
    AppActivate Application.Caption
    End Sub
    </pre>


    Regards,

    Stuart
    Attached Files Attached Files

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Minimized Excel and displaying forms (XL2002)

    In your attachment, you actually have the code:
    <pre>Sub test()
    Application.WindowState = xlMinimized
    UserForm1.Show
    End Sub </pre>


    In XL97 & Win ME, your attachment almost works, but only if there is another window open on the desktop. If everything but Excel is minimized, then Excel gets minimized, the userform is displayed, Excel blinks on the taskbar, and the userform loses focus. Are you sure you tested it with all applications minimized? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Minimized Excel and displaying forms (XL2002)

    Oops. You're right, of course. I spent half my life typing in Application.Visible and my fingers got carried away. The code is as you show it, as it is in the attachment.

    I tested the code in XL2002 and Win2K, where it seems to work perfectly happily, both with other windows open and also with all apart from Excel minimized to the taskbar, with and without the VBE window open. Whether the issue is one of XL97 or WinME, I don't know - I will test it on a W98 machine before deploying it, though, as I believe that that is the target OS. It's certainly a lesson in ensuring that an application is tested on all the platforms that it will be deployed on!

    Regards,

    Stuart

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Minimized Excel and displaying forms (XL2002)

    Just got a new computer, WinXP/XL2K, now your test form works great! Must be something in my old WinME/XL97 combo, but it does the same thing with Win98/XL97! It's not just XL97, it works fine in Win95/XL97 and WinNT/XL97. Bizzare! --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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