vba show userform upon opening, hide worksheet, but keep taskbar icon

Question

I have a userform that opens upon the opening of the workbook. Excel is also hidden so that the userform is all that is shown to the user.

Private Sub Workbook_Open()
Application.Visible = False
UserForm1.Show vbModeless
End Sub

However, this also hides the icon for Excel on the taskbar, so when a user clicks away from the userform they cannot get back into it unless using alt+tab or closes/minimises other windows that are in front of the userform. I do not want the users to do this and some may even try to open the form again (Presuming It is closed), causing re-open prompts and errors that I do not want either.

Essentially, I need an icon on the taskbar for the userform.

Once the userform is closed I have it so that Excel closes

Unload UserForm1
Application.Quit

Examples I have found on the internet for this problem don't quite achieve what I am trying to do. Changing the form to minimise and open as modal works to keep the icon in the taskbar and not let the user edit the worksheet

Application.WindowState = xlMinimized
UserForm1.Show (1)

But this has 2 problems..... 1st - the userform doesn't become the focus, 2nd - the user can click on the taskbar icon and the sheet is now visible behind the userform, which is not what I what them to be able to do.

What about this: http://stackoverflow.com/questions/15949023/displaying-an-excel-userform-as-a-button-in-the-taskbar — Robin Mackenzie, Mar 06 '17 at 12:05
I have just tried that and it didn't appear to do anything. I am not familiar with class modules though so it is probably something wrong with it's implementation. I pasted it into a new class module for the userform (Right click>Insert>class module), saved and ran the workbook again. — Aurelius, Mar 06 '17 at 12:24
I understand that the solution in the question isn't straight-forward but it very much *looks like* a duplicate of your question. The code in that question goes in the Userform module - which is actually a Class. — Robin Mackenzie, Mar 06 '17 at 12:26
Now I get an error "Invalid procedure call or argument" on line `AppActivate ("Microsoft Excel")` — Aurelius, Mar 06 '17 at 14:48

score 0 · Answer 1 · answered Mar 06 '17 at 13:53

I spent an appreciable amount of time on this task in the development of Excel-Visio application and faced with the same problem (Excel form above Visio/ Excel and VBA editor are hidden - but user can lost focus easily and only way back - Alt-Tabbing). Same problem as is!

My algorithm to solve this problem was something like this (All code in Userform class):

Private Sub UserForm_Initialize()

    'some init's above
    ToggleExcel         'Toggle excel, all windows are hidden now!
    ActivateVisio       'Visio fired and on top
    SetStandAloneForm   'Let's customize form
End Sub

So on start up we have our desired Visio and Form. On Terminate event I ToggleExcel again and minimize Visio.

ToggleExcel:

Private Function ToggleExcel()
    Static IsVBEWasVisible As Boolean

    With Application
        If .Visible = True Then
            IsVBEWasVisible = .VBE.MainWindow.Visible
            If IsVBEWasVisible Then _
                    .VBE.MainWindow.Visible = False
            .WindowState = xlMinimized
            .Visible = False
        Else
            If IsVBEWasVisible Then _
                    .VBE.MainWindow.Visible = True
            .WindowState = xlMaximized
            .Visible = True
        End If
    End With
End Function

SetStandAloneForm:

To SetStandAloneForm I declared this block of API-function:

#If VBA7 Then
    Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
    Private Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
    Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare PtrSafe Function SetFocus Lib "user32" (ByVal hWnd As Long) As Long
#Else
    Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hWnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function ShowWindow Lib "user32" (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
    Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) 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 SetFocus Lib "user32" (ByVal hWnd As Long) As Long
#End If

Actual SetStandAloneForm:

Private Function SetStandAloneForm()
    Const GWL_STYLE As Long = -16
    Const GWL_EXSTYLE As Long = -20
    Const WS_CAPTION As Long = &HC00000
    Const WS_MINIMIZEBOX As Long = &H20000
    Const WS_MAXIMIZEBOX As Long = &H10000
    Const WS_POPUP As Long = &H80000000
    Const WS_VISIBLE As Long = &H10000000
    Const WS_EX_DLGMODALFRAME As Long = &H1
    Const WS_EX_APPWINDOW As Long = &H40000
    Const SW_SHOW As Long = 5

    Dim Hwnd As Long
    Dim CurrentStyle As Long
    Dim NewStyle As Long

    If Val(Application.Version) < 9 Then
        Hwnd = FindWindow("ThunderXFrame", Me.Caption)  'XL97
    Else
        Hwnd = FindWindow("ThunderDFrame", Me.Caption)  '>XL97
    End If

    'Let's give to userform minimise and maximise buttons
    CurrentStyle = GetWindowLong(Hwnd, GWL_STYLE)
    NewStyle = CurrentStyle Or WS_MINIMIZEBOX Or WS_MAXIMIZEBOX
    NewStyle = NewStyle And Not WS_VISIBLE And Not WS_POPUP
    Call SetWindowLong(Hwnd, GWL_STYLE, NewStyle)

    'Let's give to userform a taskbar icon
    CurrentStyle = GetWindowLong(Hwnd, GWL_EXSTYLE)
    NewStyle = CurrentStyle Or WS_EX_APPWINDOW
    Call SetWindowLong(Hwnd, GWL_EXSTYLE, NewStyle)
    Call ShowWindow(Hwnd, SW_SHOW)

End Function

Where does your 3rd section of code (SetStandAloneForm) reside? It presents me with an error when placed into Userform class — Aurelius, Mar 06 '17 at 14:52
@Demo, Hey, which error? This block can be placed anywhere you want it to (but in scope of API functions), you just need to pass there `Caption` of form and replace `Me.Caption` sections inside. — CommonSense, Mar 06 '17 at 15:10
I am placing them in sequence, so I am putting it after `End Function` of the code in your 1st box and before `Private Funtion ToggleExcel()` in your 3rd code box. The error is `Compile Error: Only comments may appear after End Sub, End Function, End Property` — Aurelius, Mar 06 '17 at 15:35
Looks like we misunderstood each other. This error can occure if your place block with api functions ('#if vba7...#end if') not at the top of class/module. Are you sure that you place it at the beggining and all other functions after it? — CommonSense, Mar 06 '17 at 16:25
I have entered the code in this order now. No errors are generated now but nothing seems to be happening as there is no icon still. — Aurelius, Mar 10 '17 at 12:45
@Demo, sigh.. Can't reproduct your problem without errors. There're can be some more problems if: 1) you show your form as modal - try `yourform.show(0)` instead. 2) you have a x64 version of office and some of api function cannot be declared as i mentioned in answer, so take a look at [this](http://stackoverflow.com/questions/27900574/detect-whether-ms-office-installed-is-32bit-or-64bit-by-using-registry) **and/or** find and declare functions like [there](http://www.jkp-ads.com/Articles/apideclarations.asp) (try to find `GetWindowLong` function and see how it declared for x64). — CommonSense, Mar 10 '17 at 13:10

score 0 · Accepted Answer · edited May 23 '17 at 11:53

0

The answer posted by Gareth on this question:

Excel Useform: How to hide application but have icon in the taskbar

Worked to give me a taskbar icon and was a simple copy and paste.

Thanks all for the help.

edited May 23 '17 at 11:53

Community

1
1

answered Mar 13 '17 at 15:01

Aurelius

475
2
8
19

score -1 · Answer 3 · answered Mar 06 '17 at 11:56

-1

Rather than hide the application minimise the workbook:

ThisWorkbook.Windows(1).WindowState = xlMinimized

answered Mar 06 '17 at 11:56

Absinthe

3,258
6
31
70

sorry, I should of included this as I do already have that. The problem is then the user is able to click the taskbar icon to maximise the worksheet again – Aurelius Mar 06 '17 at 11:57

vba show userform upon opening, hide worksheet, but keep taskbar icon

3 Answers3