CODEWITHSUNDEEP
vbaexcel
Excel Developers
Excel Developers

Reputation: 2825

Displaying an Excel userform as a button in the taskbar

I want to hide the Excel button in the taskbar and display a separate button for my userform so that it feels like an application on its own. I know this has been covered a lot but I am having trouble with a specific issue: my code works fine when I step through it, but not if I let it run normally. Here is the code, which I have placed in the class module of Userform1:

Option Explicit

Private Declare Function GetWindowLong _
    Lib "user32" _
        Alias "GetWindowLongA" ( _
            ByVal hWnd As Long, _
            ByVal nIndex As Long) _
As Long

Private Declare Function SetWindowLong _
    Lib "user32" _
        Alias "SetWindowLongA" ( _
            ByVal hWnd As Long, _
            ByVal nIndex As Long, _
            ByVal dwNewLong As Long) _
As Long

Private Declare Function DrawMenuBar _
    Lib "user32" ( _
        ByVal hWnd As Long) _
As Long

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

Private Const GWL_EXSTYLE = (-20)
Private Const GWL_STYLE As Long = (-16)
Private Const WS_EX_APPWINDOW = &H40000
Private Const WS_SYSMENU As Long = &H80000
Private Const WS_MINIMIZEBOX As Long = &H20000
Private Const WS_MAXIMIZEBOX As Long = &H10000

Private Sub UserForm_Activate()

Dim lFrmWndHdl As Long
Dim lStyle As Long

lFrmWndHdl = FindWindowA(vbNullString, Me.Caption)
lStyle = GetWindowLong(lFrmWndHdl, GWL_STYLE)
lStyle = lStyle Or WS_SYSMENU
lStyle = lStyle Or WS_MINIMIZEBOX
lStyle = lStyle Or WS_MAXIMIZEBOX
SetWindowLong lFrmWndHdl, GWL_STYLE, (lStyle)
lStyle = GetWindowLong(lFrmWndHdl, GWL_EXSTYLE)
lStyle = lStyle Or WS_EX_APPWINDOW
SetWindowLong lFrmWndHdl, GWL_EXSTYLE, lStyle
DrawMenuBar lFrmWndHdl
AppActivate ("Microsoft Excel")
ThisWorkbook.Application.Visible = False

End Sub

Stepping through the code, when I step into the 2nd to last line AppActivate a separate button appears in the taskbar, and the last line hides the original button for the Excel workbook in the taskbar. I am then left with just a userform that can be maximised or minimised to the taskbar like any normal application. The problem is if I load the userform via code the separate button for the userform does not appear in the taskbar, so there are no Excel buttons left showing in the taskbar.

Upvotes: 6

Views: 9757

Answers (2)

BigRenegade
BigRenegade

Reputation: 1

Simply put the following code in your main UserForm_Initialize sub and it will minimize the Excel application window while leaving your form open on the desktop.

Private Sub minimizeWindow()
  With Application
    .WindowState = xlMinimized
  End With
End sub

Upvotes: 0

Excel Developers
Excel Developers

Reputation: 2825

To answer my own question: the problem was not in the code I posted above, but in the way the userform was loaded. It should be loaded as modeless.

Upvotes: 1

Related Questions