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.

Hi, check out the vbApplicationModal in the syntax of Loading your userform ..

Code:

---

Load UserForm1, vbApplicationModal

---

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

Sorry Firefytr,
could you expand on that a little ? I took your code literal, I put right in :
[Code]
Private Sub Workbook_open()
Load UserForm1, vbApplicationModal
UserForm1.Show
End Sub[/vCode]

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

Quote:

---

Originally Posted by debauch

Sorry Firefytr,
could you expand on that a little ? I took your code literal, I put right in :

Code:

---

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

Code:

---

UserForm1.Show vbModeless

---

Doh! .. part of the .Show method, not the Load. Got 'em mixed up in da head. LOL! Sorry 'bout that. :omg2:

Wrong number of arguments, or invalid property assignment

Code:

---

Private Sub Workbook_open()
    UserForm1.Show vbModeless
End Sub

---

Quote:

---

Originally Posted by debauch

Wrong number of arguments, or invalid property assignment

Code:

---

Private Sub Workbook_open()
    UserForm1.Show vbModeless
End Sub

---

---

That's mighty strange the code is fine! :yes

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)

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, :whistle:

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?

We posted together check out my previous tip! :yes

lol, funny. thanks, I will need this for 97.

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. :yes

Ok wipped something up for you pretty fast cause I have to be in bed now!

But this seams to work:

Code:

---

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

---

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

I'll clean it up later.

HTH, :whistle:

Sweet...I will give this a try, thanks.

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! :yes

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. :whistle:

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.

Quote:

---

Originally Posted by MWE

try this

Code:

---

UserForm1.Show vbModeless

---

---

OIC....:banghead:

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! :beerchug:

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 ???

Quote:

---

Originally Posted by debauch

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 ???

---

Hi, :yes

To be honest I didn't test my last version with 97. (The modless form does work in 97) Forgot all about the 97 version...

I''m very busy at the moment but I'll try to revisit this topic soon and test out some stuff in 97.

Later..:whistle:

thanks this thread was extremely helpful.

been searching all over the web to find a way to place forms on top of windows.

Quote:

---

Originally Posted by wqow

thanks this thread was extremely helpful.

been searching all over the web to find a way to place forms on top of windows.

---

Hi and welcome to VBAX! :hi:

Nice to see that an old thread like this one still serves good purpose in helping people.