如何让VBA代码识别Planning工作表并实现双击复制任务？

我有一个包含多工作表的工作簿，各工作表记录案件、捐赠、差旅等不同类别的任务，另有Planning工作表用于列出每周计划任务并分配每日耗时。需求是：双击其他工作表的任务单元格，自动将任务复制到Planning工作表指定区域（A21:A35）的首个空单元格，无需手动复制。

我为各任务表编写了VBA代码，但运行时触发「ActiveX组件无法创建对象」错误，定位到Set planningSheet = ThisWorkbook.Sheets("Planning")行。添加检查代码后，双击单元格会弹出提示「The 'Planning' sheet could not be found.」，但我确认表名完全正确（从可正常跳转的公式中复制而来）。

错误原因分析 #

• 工作表的**显示名(Name)和代码名(CodeName)**混淆：你看到的表名是显示名，但如果工作簿存在同名对象（如控件），或显示名包含不可见字符（空格、全角字符等），会导致Sheets("名称")匹配失败。
• On Error Resume Next会掩盖其他潜在错误，比如工作表被保护、工作簿引用异常等。

解决方法 #

1. 使用工作表代码名引用（最可靠） #

打开VBA编辑器（Alt+F11），在左侧「工程资源管理器」中找到Planning工作表，括号外的名称就是代码名（例如Sheet1 (Planning)里的Sheet1）。直接用代码名引用，无需依赖显示名：

Set planningSheet = Sheet1 ' 替换为你的Planning工作表实际代码名

2. 检查显示名的不可见字符 #

将工作表显示名复制到记事本，查看是否有多余空格、全角空格或控制字符。也可以用以下代码输出所有工作表的真实名称及长度：

Sub ListSheetNames()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        Debug.Print "显示名: " & ws.Name & " | 长度: " & Len(ws.Name)
    Next ws
End Sub

运行后按Ctrl+G打开「立即窗口」，核对Planning的名称长度是否与手动输入的一致。

3. 优化错误处理，定位真实问题 #

去掉On Error Resume Next，改用明确的错误捕获：

On Error GoTo SheetNotFound
Set planningSheet = ThisWorkbook.Sheets("Planning")
On Error GoTo 0
' 后续代码...
Exit Sub
SheetNotFound:
    MsgBox "无法找到Planning工作表，错误信息：" & Err.Description, vbCritical

修正后的完整代码示例（使用代码名版本） #

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim planningSheet As Worksheet
    Dim nextEmptyCell As Range

    ' 用代码名引用Planning工作表（替换为你的实际代码名）
    Set planningSheet = Sheet1

    ' 检查双击区域是否在目标范围B2:B100内
    If Not Intersect(Target, Me.Range("B2:B100")) Is Nothing Then
        Cancel = True ' 取消默认双击进入编辑模式的行为
        
        ' 查找首个空单元格（改用xlFormulas避免忽略公式返回空值的单元格）
        Set nextEmptyCell = planningSheet.Range("A21:A35").Find("", LookIn:=xlFormulas, LookAt:=xlWhole)
        
        If Not nextEmptyCell Is Nothing Then
            nextEmptyCell.Value = Target.Value
        Else
            MsgBox "Planning表的计划区域已填满，无法添加新任务。", vbExclamation
        End If
    End If
End Sub

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