Excel VBA问题：资产列表A列与后续工作表关联重命名代码失效求助

需求说明 #

在名为“List of Assets”的工作表中，A5及以下的A列数据需与该表之后的所有工作表一一对应。当A列数据修改时，触发Worksheet_Change事件，将“List of Assets”之后的第一个工作表命名为A5的值，第二个命名为A6的值，以此类推。

原代码存在的问题 #

• 固定工作表索引错误：硬编码从第3个工作表开始重命名，未考虑“List of Assets”的实际位置，导致对应关系错乱。
• 错误判断失效：On Error GoTo 0会重置错误编号，后续无法捕获重命名失败的错误。
• 循环逻辑混乱：终止条件计算错误，可能导致循环次数与工作表数量不匹配。
• 未禁用事件触发：修改工作表名称会再次触发Worksheet_Change，引发递归循环。
• 冗余对象引用：代码放在“List of Assets”工作表模块中，Me已指代该表，无需重复查找。
• 空值未处理：若A列单元格为空，会导致工作表命名失败。

修正后的代码 #

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim keyCells As Range
    Dim startRow As Long, lastRow As Long
    Dim sheetIndex As Long
    Dim newSheetName As String
    
    ' 禁用事件触发，避免递归
    Application.EnableEvents = False
    
    On Error GoTo ErrorHandler
    
    ' 定义监控范围：A5到A列最后一行有数据的单元格
    startRow = 5
    lastRow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
    If lastRow < startRow Then GoTo Cleanup ' 若A5以下无数据，直接退出
    
    Set keyCells = Me.Range("A" & startRow & ":A" & lastRow)
    
    ' 判断修改的单元格是否在监控范围内
    If Not Application.Intersect(Target, keyCells) Is Nothing Then
        ' 获取“List of Assets”之后第一个工作表的索引
        sheetIndex = Me.Index + 1
        
        ' 循环遍历A列数据，对应重命名工作表
        For i = startRow To lastRow
            ' 跳过空单元格
            newSheetName = Trim(Me.Cells(i, "A").Value)
            If newSheetName = "" Then
                MsgBox "A" & i & "单元格为空，跳过重命名", vbExclamation
                sheetIndex = sheetIndex + 1
                Continue For
            End If
            
            ' 判断是否存在对应工作表
            If sheetIndex > ThisWorkbook.Sheets.Count Then
                MsgBox "工作表数量不足，无法重命名A" & i & "对应的工作表", vbExclamation
                Exit For
            End If
            
            ' 重命名工作表
            ThisWorkbook.Sheets(sheetIndex).Name = newSheetName
            sheetIndex = sheetIndex + 1
        Next i
    End If

Cleanup:
    ' 恢复事件触发
    Application.EnableEvents = True
    Exit Sub

ErrorHandler:
    MsgBox "重命名失败：" & Err.Description, vbCritical
    Resume Cleanup
End Sub

代码说明 #

1. 事件禁用：开头添加Application.EnableEvents = False，避免修改工作表名称时再次触发Worksheet_Change。
2. 动态工作表索引：通过Me.Index + 1获取“List of Assets”之后第一个工作表的位置，避免固定索引的错误。
3. 空值处理：跳过A列空单元格，避免命名失败。
4. 边界判断：检查工作表数量是否足够，防止超出范围。
5. 错误捕获：统一的错误处理流程，清晰提示错误原因。
6. 冗余代码移除：直接使用Me指代当前工作表，简化逻辑。

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