+ Reply to Thread
Results 1 to 21 of 21

Make Userform on top hide workbook and use resize workbook event to maximaze and minimaze

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Make Userform on top hide workbook and use resize workbook event to maximaze and minimaze

    Hi,

    i am trying to :

    1. Run usefrom and set it on top
    2. when minimaze worbkook - keep form on top and minimaze workbook
    3. when workbook maximaze - just show userform and workbook --> on this step is not working for me.

    code:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    please help,

    Jacek
    Last edited by jaryszek; 09-26-2020 at 03:50 AM.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2013
    Posts
    3,594

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Asking for help with a VBA project which is password-protected is taking the idea of "challenge" to a whole new level!

    Greg M

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Hi Greg,

    no worries, sorry for that.

    In attachment please find file without it.

    Jacek
    Attached Files Attached Files

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Your code does nothing if the userform is visible and the workbook is maximised due to the logic in your test

    Please Login or Register  to view this content.
    You don't handle the window state of xlNormal, perhaps you should.

    Also with application on top and maximized it's difficult to debug if you only have 1 monitor.
    Cheers
    Andy
    www.andypope.info

  5. #5
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Hi,

    how should i correct it?

    I deleted elseif statement but still is not working.

    I added:

    Please Login or Register  to view this content.
    but still i have issue.

    I think this is causing the issue:
    Please Login or Register  to view this content.
    but you can see workbook is minimalized propery but show on top is not working.

    Jacek



    Jacek
    Last edited by jaryszek; 09-26-2020 at 05:35 AM.

  6. #6
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Anyone please?

    Jacek

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Please help,

    up

  8. #8
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Guys,

    please help,
    i can not manage this :/

    Jacek

  9. #9
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    What is not working for you?
    Explain what is happening and what you expect to happen.

    You appear to be setting the application on top, should it be the userform you are setting on top?

  10. #10
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Thank you Andy!

    When userform is started after workbook is minimazed - i want to keep userform on top of everything and use it all time.
    When workbook is maximazed - (user clicks to do this in small Excel icon within task bar in windows) userform do not have to be on top on eveyrthing (it should not be) but userform is also visible.

    So when user:
    1) click to minimaze workbook --> keep userform still on top /hide workbook (do not want to have additional buttons to do this on userform).
    2) click to maximaze workbook --> useform is not on top anymore but still visible.

    It is clear or should i try in other words?

    Thanks!
    Jacek

  11. #11
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    So it is the userform, and not the excel application, that you want to be setting on top.

    You need to change the windows handle you are passing to that of the userform.
    https://colinlegg.wordpress.com/2016...userforms-vba/

    In your windowstate event you should deal with all 3 states. So you probably what to treat Maximize and Normal in the same way.

  12. #12
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    thanks,

    In your windowstate event you should deal with all 3 states. So you probably what to treat Maximize and Normal in the same way.
    i do not udenrstand what is normalView?

    You need to change the windows handle you are passing to that of the userform.
    https://colinlegg.wordpress.com/2016...userforms-vba/
    on this article there is possiblity to find userform window.

    So you mean here:

    Please Login or Register  to view this content.
    i should pass useform .

    Ok i added:

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    but still what i have to do put here?:


    Please Login or Register  to view this content.
    how to get userfoem handle?
    Maybe i could use hidden sheet , write useform handle there and just use it directy from cell?

    What is best solution here?

    Jacek
    Attached Files Attached Files

  13. #13
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    In the article is a function to get userform handle. Use that instead of the Excel application handle.

    Excel window, like all windows, has 3 states.

    Maximized, fills the screen
    Minimized, closed to start bar
    Normal, open but resizable


    Please Login or Register  to view this content.

  14. #14
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    ok thanks!

    But still not working for me

    I changed code:

    Please Login or Register  to view this content.
    and now i now that this is userform.

    But this is not triggering:
    Please Login or Register  to view this content.
    I mean is hidinh workbook together with userform...

    Please help,
    Jacek
    Attached Files Attached Files

  15. #15
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim


  16. #16
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    thanks Andy but this is entirely new code and class.
    I hope that anybody here can help with what i have and explain why this is not working.

    Why my code is not working?
    What is wrong?
    It should work without any issues...

    Jacek
    Last edited by jaryszek; 09-30-2020 at 03:34 AM.

  17. #17
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Like Guy said from article:

    The problem can be solved rather simple, by using some code that calls a couple of Windows API functions. All code could go inside the userform's code window. Unfortunately, because I chose to make the form topmost, we must handle the fact that another application might become the foreground window. For example: you are running Excel with the userform showing and then you open Word. In the simple case, the userform will stay on top of Word, which is not what we want.
    This is want i do not want to achevie! I want to have userform alwasy on top of another applications.
    And when workook minimaze - still have userform on top, when workbook maximazed - turn off userform on top.

    Just it.

    Jacek

  18. #18
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,865

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    If you check that example, and it works how you need it to, then you can see at how the code differs and understand why the current code is not working.

  19. #19
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Hi Andy,

    it doesnt work like i want.

    I wrote above why...

    edit: And i do not understand this code entirely. There is a lot of code when to set up useform on top i am using just few lines of code...

    Jacek
    Last edited by jaryszek; 09-30-2020 at 03:46 AM.

  20. #20
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    ah i found

    Please Login or Register  to view this content.

  21. #21
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016
    Posts
    2,666

    Re: Make Userform on top hide workbook and use resize workbook event to maximaze and minim

    Thank you Andy!

    Is working like a charm!

    Jacek

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Workbook Open event - userform gets displayed over another workbook
    By hemantparmar in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-18-2019, 11:56 AM
  2. [SOLVED] Hide workbook that runs the Userform only.
    By KelFofo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-13-2016, 06:33 PM
  3. [SOLVED] Best way to hide workbook behind userform
    By VBA FTW in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-08-2014, 03:38 PM
  4. Hide a UserForm on workbook deactivation
    By aca in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-16-2009, 03:22 PM
  5. Open another workbook for multiple userform event use.
    By D_Rennie in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-13-2009, 01:30 AM
  6. Hide Sheet using Workbook Open Event
    By johnhildreth@citynet.net in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-09-2005, 03:30 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1