[RESOLVED] Display Excel Userform in 'unfocused' state

[VBAhack]

Is it possible to display an Excel Userform such that is appears initially in a de-highlighted state? I'm not sure, but I think the term is 'focus'

I currently show a modeless userform triggered from the workbook open event but it always appears in a highlighted state.

Private Sub Workbook_Open()
    OptionList.Show vbModeless  'appears in highlighted state
End Sub

[RobDog888]

Private Sub Workbook_Open()
    OptionList.Show vbModeless
    Application.WindowState = xlMinimized
    Application.WindowState = xlMaximized
End Sub

[VBAhack]

RobDog,

That's an interesting trick. Thanks!

[RobDog888]

I know its very elegant but as long as your opening the file any flashing from it minimizing/maximizing will not be noticeable.

You could also get fancy and use a couple of APIs to get the solution but more work then its worth unless your needing it to be inactive when showing a userform and not in combination of opening the file and showing the userform.

[VBAhack]

Actually, the flashing is very noticeable. You can readily see the window being minimized, then maximized. If the API approach isn't too nasty I'd like to give it a try in the interest of elegance. Can you explain?

P.S. I want the Userform to be inactive all the time except when clicked.

[VBAhack]

Robdog,

I managed to develop a workaround that has inperceptable flickering, but it means creating another userform:

Private Sub Workbook_Open()
    OptionList.Show vbModeless
    UserForm1.Show vbModeless           'dummy userform
    Unload UserForm1
End Sub

I'm still interested in the API approach if you could post an example or a link. Thanks!

[RobDog888]

Ok, here it is. Just paste the code behind your userform and viola!

Option Explicit
'Code written by RobDog888
'Behind your userform:
Private Declare Sub 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)
 
Const SW_SHOWNORMAL    As Long = 1
Const SW_SHOWMAXIMIZED As Long = 3
 
Const HWND_TOPMOST     As Long = -1
Const HWND_NOTOPMOST   As Long = -2
Const HWND_BOTTOM      As Long = 1
 
Const SWP_NOSIZE       As Long = &H1
Const SWP_NOMOVE       As Long = &H2
Const SWP_NOACTIVATE   As Long = &H10
Const SWP_SHOWWINDOW   As Long = &H40
 
Private mbFirst        As Boolean
 
Private Sub UserForm_Activate()
    'Show the window if first time
    If mbFirst = True Then
        SetWindowPos Application.hWnd, HWND_BOTTOM, 0, 0, 0, 0, SWP_SHOWWINDOW Or SWP_NOMOVE Or SWP_NOSIZE
    Else
        mbFirst = False
    End If
End Sub
 
Private Sub UserForm_Initialize()
    mbFirst = True
End Sub

[VBAhack]

RobDog,

Brilliant! Thanks. I decided to condense things a bit:

Option Explicit
 
Private Declare Sub 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)
Private mbFirst As Boolean
 
Private Sub UserForm_Activate()
    'Show the window if first time
    If mbFirst = True Then
        SetWindowPos Application.hWnd, CLng(1), 0, 0, 0, 0, CLng(&H40) Or CLng(&H2) Or CLng(&H1)
    Else
        mbFirst = False
    End If
End Sub
 
Private Sub UserForm_Initialize()
    mbFirst = True
End Sub

[RobDog888]

Thanks but using the consts for the parameters are a better method as you dont have to remember what they mean.

Also, if you just pass the consts with the "&" long designator and add the hex Ors its evevn shorter.

SetWindowPos Application.hWnd, 1&, 0, 0, 0, 0, 67&

[VBAhack]

Understood. Most appreciated!