如何通过Personal.xlsb实现全局BeforeDoubleClick/RightClick事件自定义？

Absolutely feasible! This is a perfect scenario to put Personal.xlsb to work for global Excel functionality, and adding a userform to switch behaviors makes it super flexible. Let’s walk through exactly how to build this:

1. 核心原理：Application级全局事件捕获 #

Personal.xlsb loads automatically when Excel starts, so we can leverage application-level events to intercept actions (like double-clicks or right-clicks) across every open workbook. This is the key to making your functionality work globally, not just for a single file.

2. Step 1: 创建全局事件监听类模块 #

First, we need a class module to hook into Excel's application-wide events:

• Open the VBA Editor in Excel (Alt+F11), then navigate to Personal.xlsb in the Project Explorer.
• Right-click Personal.xlsb → Insert → Class Module.
• Rename the class module clsAppEvents (use the Properties window, F4, to change the name).
• Paste this code into the class module:

Public WithEvents xlApp As Application

Private Sub xlApp_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' 根据全局模式判断是否执行自定义逻辑
    If DoubleClickMode = 1 Then
        Target.Interior.Color = vbGreen
        Cancel = True ' 取消默认双击行为（比如进入单元格编辑模式）
    End If
    ' 如果模式为0，不做任何处理，保留Excel默认双击行为
End Sub

Private Sub xlApp_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    If RightClickMode = 1 Then
        ' 这里替换成你想要的右键自定义逻辑，比如弹出特定菜单、设置其他格式等
        MsgBox "自定义右键行为已触发！"
        Cancel = True ' 隐藏默认右键菜单
    End If
End Sub

3. Step 2: 初始化全局事件监听 #

Next, connect our class to Excel's application when Personal.xlsb opens:

• Open the ThisWorkbook module in Personal.xlsb.
• Paste this code:

Dim appEvents As clsAppEvents

Private Sub Workbook_Open()
    ' 初始化事件监听类
    Set appEvents = New clsAppEvents
    Set appEvents.xlApp = Application
    
    ' 设置默认模式：双击设为绿色开启，右键自定义关闭
    DoubleClickMode = 1
    RightClickMode = 0
    
    ' 以非模态方式显示控制窗体（这样你可以在操作Excel的同时切换模式）
    UserForm1.Show vbModeless
End Sub

4. Step 3: 添加全局模式变量 #

Create a standard module to store our mode settings (so all parts of the code can access them):

• Right-click Personal.xlsb → Insert → Module.
• Paste this code:

' 全局变量，用于跟踪当前行为模式
Public DoubleClickMode As Integer ' 1=双击设为绿色，0=默认行为
Public RightClickMode As Integer ' 1=自定义右键，0=默认右键菜单

' 切换模式的辅助子程序，供窗体按钮调用
Sub SetDoubleClickGreen()
    DoubleClickMode = 1
End Sub

Sub SetDoubleClickDefault()
    DoubleClickMode = 0
End Sub

Sub SetRightClickCustom()
    RightClickMode = 1
End Sub

Sub SetRightClickDefault()
    RightClickMode = 0
End Sub

5. Step 4: 制作模式切换用户窗体 #

Build a userform to let you toggle behaviors with buttons:

• Right-click Personal.xlsb → Insert → UserForm.
• Add 4 Command Buttons to the form (use the Toolbox, Ctrl+T if it's hidden).
• Set each button's Caption property (via F4) to:
  • "双击：设置单元格为绿色"
  • "双击：恢复默认行为"
  • "右键：启用自定义行为"
  • "右键：恢复默认菜单"
• Double-click each button to add its click event code:

Private Sub CommandButton1_Click()
    SetDoubleClickGreen
    MsgBox "双击模式已切换为：设置单元格为绿色"
End Sub

Private Sub CommandButton2_Click()
    SetDoubleClickDefault
    MsgBox "双击模式已切换为：默认行为"
End Sub

Private Sub CommandButton3_Click()
    SetRightClickCustom
    MsgBox "右键模式已切换为：自定义行为"
End Sub

Private Sub CommandButton4_Click()
    SetRightClickDefault
    MsgBox "右键模式已切换为：默认菜单"
End Sub

6. 运行注意事项 #

• 宏安全设置: 确保Excel的宏设置允许加载带宏的工作簿（Personal.xlsb通常位于受信任文件夹中，默认会被允许）。
• 非模态窗体: 使用vbModeless可以让窗体保持打开状态，不影响你操作Excel。
• 自定义右键逻辑: 你可以把xlApp_BeforeRightClick中的MsgBox替换成任何你需要的操作，比如设置单元格格式、运行特定宏等。

This setup will let you toggle between custom and default behaviors for both double-clicks and right-clicks across every workbook you open—exactly what you're looking for!

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