VBA打开时显示用户形式，隐藏工作表，但请保留任务栏图标

Question

我有一个用户形式，该用户形式在工作簿打开时打开。excel也被隐藏了，因此用户形式全部显示给用户。

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

但是，这也掩盖了任务栏上的Excel的图标，因此，当用户从用户形式上单击Userform时，除非使用Alt Tab或关闭/最小化用户形式前面的其他窗口，否则无法返回它。我不希望用户这样做，有些人甚至可能尝试再次打开表单（假定它已关闭），从而导致重新打开的提示和错误。

本质上，我需要在用户形式的任务栏上图标。

用户形式关闭后，我就有了，以便Excel关闭

Unload UserForm1
Application.Quit

我在Internet上发现的有关此问题的示例并没有达到我要做的事情。更改表单以最小化和打开，因为模态工作以将图标保留在任务栏中，而不让用户编辑工作表

Application.WindowState = xlMinimized
UserForm1.Show (1)

但这有2个问题..... 1st-用户形式并没有成为焦点，第二个 - 用户可以单击任务栏图标，现在可以在用户形式后面看到工作表，这不是我什么什么他们能够做。

Answer 1

我在开发Excel -Visio应用程序上花费了大量时间在此任务上，并且面临着相同的问题（Excel表单上方是Visio/Excel和VBA编辑器，但用户是用户很容易失去焦点，而唯一的返回 - tabbing）。与IS相同的问题！

我解决此问题的算法是这样的（用户形式类中的所有代码）：

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

因此，在启动时，我们有所需的visio和形式。在Terminate事件上，我再次 ToggleExcel并最大程度地减少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

SetStandAnstalOneform ：

setStandalOneform我声明了这个api功能的块：

#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

实际的setstandAnstalOneform：

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

Answer 2

Gareth在此问题上发布的答案：

excel useform：如何隐藏应用程序但在任务栏中具有图标

努力给我一个任务栏图标，是一个简单的副本和粘贴。

感谢所有的帮助。

Answer 3

而不是隐藏应用程序最小化工作簿：

ThisWorkbook.Windows(1).WindowState = xlMinimized