Sheet loses focus when userform is open switching between windows

[ExcelLover7]

Hi,

I know this is a topic that has been covered many times in various forums on this site, but after trying just about everything I'm capable of, I'm still struggling and would be very grateful for some help on this.

Basically, I have a floating userform that I like to have permanently running in Excel. It has various number/date formatting buttons and other useful macros I've created in the past.

The userform is modeless, and when I open the userform in Excel it nicely appears as an unselected window ontop of Excel. The problem occurs when I then move to Outlook or another application and then flick back again to Excel. When I do this, the userform takes precedence and is selected, rather than the worksheet. This becomes a real pain when flicking between 2 Excel workbooks, especially when copying and pasting between 2 sheets, as I have to 'click' onto the sheet to take focus off the userform.

I've tried various things like...

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
AppActivate "Microsoft Excel"

...in the 'ThisWorkbook' sheet in the userform, but no joy.

My knowledge of VB is ok, but nowhere near as advanced as I'd like it to be, so replies may have to be in simple language if possible!

Any help that can be given on this is much appreciated.

 

[John_w]

See if this Windows API code to keep the userform 'always on top' works for your situation.

Put this code in a standard module:

Public Const SWP_NOMOVE = &H2
Public Const SWP_NOSIZE = &H1

Public Const HWND_TOP = 0
Public Const HWND_BOTTOM = 1
Public Const HWND_TOPMOST = -1
Public 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 uFlags As Long) As Long

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
    (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

Put this code in the userform module:

Private Sub UserForm_Initialize()
    AlwaysOnTop Me.caption
End Sub

Private Sub AlwaysOnTop(caption As String)

    Dim ret As Long
    Dim hWnd As Long
    
    hWnd = FindWindow(vbNullString, caption)
    ret = SetWindowPos(hWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE)
    
End Sub

As you can see, UserForm_Initialize calls AlwaysOnTop with the userform's caption string. Place this call in an appropriate place in your own UserForm_Initialize procedure.

 

[ExcelLover7]

Thanks for such a speedy reply John_w! I'm pleased to say this worked perfectly. I get the general gist of what this has done but the code is beyond me. Either way, the end result is great and will save me hours of frustration.

Cheers!