Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 23

Thread: Solved: Keep userform on top at all times

  1. #1
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location

    Solved: Keep userform on top at all times

    Hello,
    Not sure if this is a quick fix or not. I am trying to keep an applicaiton I have designed on top of all windows at all times. I am not sure how to do so, I searched goole, alta-visa, the forums inside this site and unsuccessful. Can any excel guru's lend a hand in this one ?
    If it helps, I do have a class module I use in most applications, mostly for the taskbar icon, it was not designed by me, but I do use it. I cannot find in the Class module (which rips the windows API ... i think) where this would fall under, if at all.

    Thanks guys/gals.

  2. #2
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Hi, check out the vbApplicationModal in the syntax of Loading your userform ..

    [vba]Load UserForm1, vbApplicationModal[/vba]

    You also have other Modal's, i.e. vbSystemModal, etc. Research the Help files for a better explanation.

  3. #3
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    Sorry Firefytr,
    could you expand on that a little ? I took your code literal, I put right in :
    [vba]
    Private Sub Workbook_open()
    Load UserForm1, vbApplicationModal
    UserForm1.Show
    End Sub[/vba]

    It did not work. is vbapplicationmodal built right in somwehere ? or do I need to find a file to import to use it ???

  4. #4
    VBAX Expert
    Joined
    Feb 2005
    Posts
    929
    Location
    Quote Originally Posted by debauch
    Sorry Firefytr,
    could you expand on that a little ? I took your code literal, I put right in :

    Private Sub Workbook_open()
    Load UserForm1, vbApplicationModal
    UserForm1.Show
    End Sub

    It did not work. is vbapplicationmodal built right in somwehere ? or do I need to find a file to import to use it ???
    try this

    [vba]UserForm1.Show vbModeless[/vba]
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.

  5. #5
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Doh! .. part of the .Show method, not the Load. Got 'em mixed up in da head. LOL! Sorry 'bout that.

  6. #6
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    Wrong number of arguments, or invalid property assignment
    [vba]
    Private Sub Workbook_open()
    UserForm1.Show vbModeless
    End Sub[/vba]

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by debauch
    Wrong number of arguments, or invalid property assignment

    Private Sub Workbook_open()
    UserForm1.Show vbModeless
    End Sub
    That's mighty strange the code is fine!

    Where did you paste the code? (I hope in ThisWorkbook classmodule)

    What version of excel are you running this on? (If Excel 97 then its simple...it doesn't have a Modeless form so then the error is correct)
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  8. #8
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    I see you are here and if you do happen to have Excel 97 try out Steven Bullen's modeless form for 97:
    http://www.bmsltd.ie/DLCount/DLCount...delessForm.zip

    HTH,
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  9. #9
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    I was creating the application at work, which uses '97. I am using 2003 at home, when I got home, the code worked fine. There may be a difference somewhere bewtween versions.

    Ok , I greatly appreciate the code. I know that will be useful in future applications.....but, (there is always a but) is there anyway to have the userform stay on top of ALL windows/applications (internet explorer, lotus notes etc) ?? If not I can work around it, but I believe I have seen apps do this b4 . Any ideas?

  10. #10
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    We posted together check out my previous tip!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  11. #11
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    lol, funny. thanks, I will need this for 97.

  12. #12
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by debauch
    lol, funny. thanks, I will need this for 97.
    Ok you're welcome.

    I'll think about your question to keep the Userform Always on top even when switching apps.
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  13. #13
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Ok wipped something up for you pretty fast cause I have to be in bed now!

    But this seams to work:[vba]
    Option Explicit
    'API function to enable/disable the Excel Window
    Private Declare Function FindWindowA Lib "user32" _
    (ByVal lpClassName As String, ByVal lpWindowName As String) As Long

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

    Private Declare Function SetWindowPos Lib "user32" ( _
    ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
    ByVal X As Long, ByVal Y As Long, ByVal cx As Long, _
    ByVal cy As Long, ByVal wFlags As Long) As Long

    Private Const SWP_NOSIZE = &H1
    Private Const SWP_NOMOVE = &H2
    Private Const FLAGS As Long = SWP_NOMOVE Or SWP_NOSIZE
    Private Const HWND_TOPMOST = -1
    Private Const HWND_NOTOPMOST = -2
    Private mlHWnd As Long
    Private mbDragDrop As Boolean
    Private FormHWnd As Long

    Private Sub cmdNotTop_Click()
    SetWindowPos FormHWnd, HWND_NOTOPMOST, 0, 0, 0, 0, FLAGS
    End Sub

    Private Sub cmdTop_Click()
    SetWindowPos FormHWnd, HWND_TOPMOST, 0, 0, 0, 0, FLAGS
    End Sub

    Private Sub UserForm_Activate()
    On Error Resume Next
    'Find the Excel main window
    mlHWnd = FindWindowA("XLMAIN", Application.Caption)
    FormHWnd = FindWindowA(vbNullString, Me.Caption)
    Call cmdTop_Click
    'Enable the Window - makes the userform modeless
    EnableWindow mlHWnd, 1
    mbDragDrop = Application.CellDragAndDrop
    Application.CellDragAndDrop = False
    End Sub

    Private Sub btnOK_Click()
    Application.CellDragAndDrop = mbDragDrop
    Call cmdNotTop_Click
    Unload Me
    End Sub
    [/vba]

    It's the form of Steven Bullen I've altered.

    I'll clean it up later.

    HTH,
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  14. #14
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    Sweet...I will give this a try, thanks.

  15. #15
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by debauch
    Sweet...I will give this a try, thanks.
    You're welcome let me know how it works out for yah!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  16. #16
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Hi Stripped out a bit more in the attachment.

    You're question was about keeping the form on top all the time so for that the Form doesn't have to be modeless. (But it could be of course if you like)

    Now it's no more and you only have the nessecary stuff to keep it ON TOP or not....

    HTH.
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  17. #17
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    That worked perfect (trying at home in 2003 - fingers crossed for '97). I had to use application.visible = false so when I clicked on the userform it would not keep switching back to excel.

    There is so many generic stationary / typing I use at work, so this function will help keep it on top, and I have pre-determined stationaries to paste into lotus on demand. It helps a little....I am so lazy...lol.

    Thanks a million. Next question I will save...search around a bit first. I wanna know if I can implement Fkey commands into my apps. I will search around though, Thanks Mos.

  18. #18
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by MWE
    try this

    [vba]UserForm1.Show vbModeless[/vba]
    OIC....

  19. #19
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    Quote Originally Posted by debauch
    Thanks a million. Next question I will save...search around a bit first. I wanna know if I can implement Fkey commands into my apps. I will search around though, Thanks Mos.
    Glad to see its working out for yah.

    You're most welcome!
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  20. #20
    VBAX Tutor
    Joined
    Mar 2005
    Posts
    221
    Location
    Ok, I had to bring up old topics, but I brought in a copy of one that worked at home, to work (Excel 97), using the "form on top.zip" code, and it will not work in 97...is there a way around this, an add-in, or different code ? Or should I beg my work to upgrade to '03 ???

Posting Permissions

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