keep userform on topmost view 1

[CClark]

Hi there. Im new to the VBA field, and have written a bit of code.

What Im trying to find out is... Is it possible to Create a userform in Excel, or Word 97 that stays ontop of any other application that is running ???

Ie... just like the Microsoft office toolbar.

Why I am running this is, I have written several Excel programs, and have now merged them into 1 excel file. My users are annoyed because they have to "ALT-TAB" between programs all the time.

Cheers all !!!
Campbell

 

[dsi]

Put this code in your main module:

Const SWP_NOSIZE = &H1
Const SWP_NOMOVE = &H2
Const HWND_TOPMOST = -1
Const HWND_NOTOPMOST = -2
Public 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

Public Sub ForceWindowOnTop(hwnd As Long, bTrueFalse As Boolean)
    Dim i
    If bTrueFalse = True Then
        i = SetWindowPos(hwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOSIZE Or SWP_NOMOVE)
    Else
        i = SetWindowPos(hwnd, HWND_NOTOPMOST, 0, 0, 0, 0, SWP_NOSIZE Or SWP_NOMOVE)
    End If
End Sub

Then, if your form:

Private Sub Form_Load()
    Call ForceWindowOnTop(Me.hwnd, True)
End Sub

Hope this helps!

 

[CClark]

Thanks DSI for this.

Unfortunately, I am having problems running the actual code.

I am using Excel 97, and am just using the Userforms in VBA.

I have created a Module called "Module 1", and pasted the first part of the code in there. ( have also tried placing this code in the "This Workbook" part)

Then created a Userform called "Userform1"

Now ... I could not create a private sub called "Private Sub UserForm_Load()" as this never did anything. (changed the code to "msgbox("DID THIS FORM LOAD&quot", but everytime I loaded the form, the msgbox never appeared.)

So I created a sub called "Private Sub UserForm_Initialize()"

and loaded that code in there. Now... I keep getting an error that points to the ".hwnd" in the code. "Call ForceWindowOnTop(Me.hwnd, True)"

Error message is "method or data member not found"

Can someone explain to me what the me.hwnd, or hwnd is ??? If I know what it does, then perhaps I can fix it ???

Apologize for my ignorance, but I would really appreciate it, if you could just help me a little more.

Cheers in advance.
Campbell

 

[dsi]

I guess I overlooked the Excel factor! First, there is no On Load event in Excel. You would use the On Activate event for this procedure. Unfortunately, the Me.Hwnd property is not available in Excel. I use this with VB6. The 'Me' part is the form object. The Hwnd property is the handle to the form, represented as a Long. Unfortunately, this property is not available with Excel forms.

To solve this problem, you will have to get the handle of the form using some other method. I will try to look into this for you, but my time is rather tight today. Once you get the handle, just call the ForceWindowOnTop() function, passing the handle in place of Me.Hwnd.