PDA

View Full Version : Solved: Keep userform on top at all times



debauch
09-07-2005, 07:45 AM
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.

Zack Barresse
09-07-2005, 08:38 AM
Hi, check out the vbApplicationModal in the syntax of Loading your userform ..

Load UserForm1, vbApplicationModal

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

debauch
09-07-2005, 11:22 AM
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 ???

MWE
09-07-2005, 11:42 AM
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

UserForm1.Show vbModeless

Zack Barresse
09-07-2005, 12:57 PM
Doh! .. part of the .Show method, not the Load. Got 'em mixed up in da head. LOL! Sorry 'bout that. :omg2:

debauch
09-07-2005, 01:08 PM
Wrong number of arguments, or invalid property assignment

Private Sub Workbook_open()
UserForm1.Show vbModeless
End Sub

MOS MASTER
09-07-2005, 01:25 PM
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! :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)

MOS MASTER
09-07-2005, 02:54 PM
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.asp?file=ModelessForm.zip

HTH, :whistle:

debauch
09-07-2005, 02:55 PM
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?

MOS MASTER
09-07-2005, 02:55 PM
We posted together check out my previous tip! :yes

debauch
09-07-2005, 03:12 PM
lol, funny. thanks, I will need this for 97.

MOS MASTER
09-07-2005, 03:16 PM
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

MOS MASTER
09-07-2005, 03:37 PM
Ok wipped something up for you pretty fast cause I have to be in bed now!

But this seams to work:
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:

debauch
09-07-2005, 03:41 PM
Sweet...I will give this a try, thanks.

MOS MASTER
09-08-2005, 01:52 PM
Sweet...I will give this a try, thanks.

You're welcome let me know how it works out for yah! :yes

MOS MASTER
09-08-2005, 02:31 PM
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:

debauch
09-08-2005, 05:45 PM
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.

sheeeng
09-09-2005, 04:40 AM
try this

UserForm1.Show vbModeless

OIC....:banghead:

MOS MASTER
09-10-2005, 07:29 AM
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:

debauch
09-14-2005, 12:11 PM
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 ???

MOS MASTER
09-14-2005, 03:59 PM
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:

wqow
06-15-2008, 08:52 PM
thanks this thread was extremely helpful.

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

MOS MASTER
06-16-2008, 02:04 PM
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.