Excel 2013基于WorkbookOpen事件的专属用户窗体VBA实现问询

Hey Ricardo, I’ve built exactly this kind of setup before—locking a VBA form to a standalone Excel instance with those strict window controls is totally doable. Let’s break down the code and how it works step by step:

Step 1: ThisWorkbook Module Code #

This is where we handle the workbook open event, check for a standalone instance, and initialize the form. We use WithEvents to listen for Excel app-level changes too:

Option Explicit

' Track Excel app events to block new workbooks later
Private WithEvents App As Application

Private Sub Workbook_Open()
    Set App = Application
    
    ' Check if this is the only workbook in the instance
    If Application.Workbooks.Count > 1 Then
        MsgBox "Whoa, this needs to run in a standalone Excel instance! Use the custom shortcut we set up instead.", vbExclamation, "Instance Alert"
        ' Optional: Auto-close if they don't use the right shortcut
        ' ThisWorkbook.Close SaveChanges:=False
        Exit Sub
    End If
    
    ' Launch and configure the user form
    With YourUserFormName ' Replace with your actual form name!
        ' Disable the top-right close button (uses API below)
        DisableCloseButton .hwnd
        ' Lock form in place so it can't be moved
        .StartUpPosition = 0
        .Top = 150 ' Pick a fixed top position
        .Left = 200 ' Pick a fixed left position
        ' Keep form always on top of this Excel instance
        .Show vbModeless
        SetWindowPos .hwnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE
    End With
End Sub

' Block users from opening new workbooks in this dedicated instance
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    MsgBox "This instance is only for our custom tool—no other workbooks allowed!", vbExclamation, "No Go"
    Wb.Close SaveChanges:=False
End Sub

Step 2: Standard Module for API Helpers #

We need Windows API calls to handle the close button and topmost behavior—add this to a new standard module:

Option Explicit

' Windows API declarations for window control
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 wFlags As Long) As Long
Public Declare Function GetSystemMenu Lib "user32" (ByVal hwnd As Long, ByVal bRevert As Long) As Long
Public Declare Function RemoveMenu Lib "user32" (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Long

' Constants for API calls
Public Const HWND_TOPMOST = -1
Public Const SWP_NOMOVE = &H2
Public Const SWP_NOSIZE = &H1
Public Const SC_CLOSE = &HF060&
Public Const MF_BYCOMMAND = &H0&

' Helper to disable the form's close button
Sub DisableCloseButton(ByVal hwnd As Long)
    Dim hSysMenu As Long
    hSysMenu = GetSystemMenu(hwnd, False)
    If hSysMenu Then
        RemoveMenu hSysMenu, SC_CLOSE, MF_BYCOMMAND
    End If
End Sub

Key Details to Note #

• Standalone Instance Setup: Tell users to create a shortcut with the /x parameter to force Excel to open a new instance. For example:

  "C:\Program Files\Microsoft Office\Office15\EXCEL.EXE" /x "C:\Path\To\Your\File.xlsm"
  

  This guarantees the workbook opens alone.
• Form Locking: By setting StartUpPosition=0 and fixed Top/Left, users can’t drag the form. The API call removes the close button entirely (not just disabling the event).
• Topmost Behavior: SetWindowPos with HWND_TOPMOST keeps the form above everything else in this Excel instance—even if users click back to the worksheet.
• Extra Guard: The App_NewWorkbook event blocks users from adding other workbooks to the instance later, keeping your tool isolated.

内容的提问来源于stack exchange，提问作者Ricardo A