如何为不同工作表分配VBA代码？多工作表设备目录开发问题

问题根源 #

你代码里的Sheet1是工作表的代码名称（VBA编辑器属性窗口中的(Name)字段），不是Excel界面显示的工作表标签名。所以哪怕修改了工作表标签，只要代码名称还是Sheet1，代码就只会作用于它。

两种优化方案 #

方案1：让代码作用于点击按钮所在的工作表 #

把代码中固定的Sheet1替换为动态获取按钮所在的工作表，避免依赖激活状态。修改后的代码示例：

Dim CustRow As Long, ProdCol As Long, ProdRow As Long, LastProdRow As Long, LastResultRow As Long
Dim DataType As String
Dim ItemShp As Shape

Sub Customize_Open()
    ' 直接获取按钮所在的工作表，比ActiveSheet更严谨
    Dim targetSheet As Worksheet
    Set targetSheet = Application.Caller.Parent
    
    With targetSheet
        'limpar grupos menos amostra
        For Each ItemShp In .Shapes
            On Error Resume Next
            If InStr(ItemShp.Name, "Picture") <> Empty Then ItemShp.Delete
            If InStr(ItemShp.Name, "ItemGrp") <> Empty Then ItemShp.Delete
            On Error GoTo 0
        Next ItemShp
        On Error Resume Next
        .Shapes("SampleGrp").Visible = msoCTrue
        .Shapes("SampleGrp").Ungroup
        On Error GoTo 0
        'colocar em free floating
        For Each ItemShp In .Shapes
            If InStr(ItemShp.Name, "Sample") > 0 Then
                ItemShp.Placement = xlFreeFloating
                ItemShp.Visible = msoCTrue
            End If
        Next ItemShp
        .Shapes("CloseCustBtn").Visible = msoCTrue
        .Shapes("ResetBtn").Visible = msoCTrue
        .Shapes("LabelTemplate").Visible = msoCTrue
        .Shapes("OpenCustBtn").Visible = msoFalse
        .Range("C:F").EntireColumn.Hidden = False
    End With
End Sub

Sub Customize_Close()
    Dim targetSheet As Worksheet
    Set targetSheet = Application.Caller.Parent
    
    With targetSheet
        On Error Resume Next
         .Shapes("SampleGrp").Placement = xlFreeFloating
        .Shapes("SampleGrp").Visible = msoFalse
        On Error GoTo 0
        .Shapes("CloseCustBtn").Visible = msoFalse
        .Shapes("LabelTemplate").Visible = msoFalse
        .Shapes("ResetBtn").Visible = msoFalse
        .Shapes("OpenCustBtn").Visible = msoCTrue
           .Range("C:F").EntireColumn.Hidden = True
    End With
End Sub

说明：Application.Caller.Parent能直接定位到按钮所在的工作表，不会因为用户切换工作表而出错。

方案2：给宏添加参数，绑定按钮时指定工作表 #

把核心宏改成接受Worksheet类型参数，再写简单的包装宏绑定到按钮，灵活指定目标工作表。

1. 修改核心宏代码：

Dim CustRow As Long, ProdCol As Long, ProdRow As Long, LastProdRow As Long, LastResultRow As Long
Dim DataType As String
Dim ItemShp As Shape

Sub Customize_Open(targetSheet As Worksheet)
    With targetSheet
        ' 原逻辑不变，仅将Sheet1替换为传入的targetSheet
        'limpar grupos menos amostra
        For Each ItemShp In .Shapes
            On Error Resume Next
            If InStr(ItemShp.Name, "Picture") <> Empty Then ItemShp.Delete
            If InStr(ItemShp.Name, "ItemGrp") <> Empty Then ItemShp.Delete
            On Error GoTo 0
        Next ItemShp
        On Error Resume Next
        .Shapes("SampleGrp").Visible = msoCTrue
        .Shapes("SampleGrp").Ungroup
        On Error GoTo 0
        'colocar em free floating
        For Each ItemShp In .Shapes
            If InStr(ItemShp.Name, "Sample") > 0 Then
                ItemShp.Placement = xlFreeFloating
                ItemShp.Visible = msoCTrue
            End If
        Next ItemShp
        .Shapes("CloseCustBtn").Visible = msoCTrue
        .Shapes("ResetBtn").Visible = msoCTrue
        .Shapes("LabelTemplate").Visible = msoCTrue
        .Shapes("OpenCustBtn").Visible = msoFalse
        .Range("C:F").EntireColumn.Hidden = False
    End With
End Sub

Sub Customize_Close(targetSheet As Worksheet)
    With targetSheet
        On Error Resume Next
         .Shapes("SampleGrp").Placement = xlFreeFloating
        .Shapes("SampleGrp").Visible = msoFalse
        On Error GoTo 0
        .Shapes("CloseCustBtn").Visible = msoFalse
        .Shapes("LabelTemplate").Visible = msoFalse
        .Shapes("ResetBtn").Visible = msoFalse
        .Shapes("OpenCustBtn").Visible = msoCTrue
           .Range("C:F").EntireColumn.Hidden = True
    End With
End Sub

2. 编写包装宏（带参数的宏无法直接绑定按钮）：

' 绑定到Sheet2的Open按钮
Sub Call_Customize_Open_Sheet2()
    Customize_Open Sheet2 ' Sheet2是目标工作表的代码名称
End Sub

' 绑定到Sheet2的Close按钮
Sub Call_Customize_Close_Sheet2()
    Customize_Close Sheet2
End Sub

' 同理为其他工作表编写对应包装宏
Sub Call_Customize_Open_Sheet3()
    Customize_Open Sheet3
End Sub

Sub Call_Customize_Close_Sheet3()
    Customize_Close Sheet3
End Sub

将对应的包装宏绑定到各工作表的按钮即可。

额外提示 #

可以在VBA编辑器中修改工作表的代码名称（选中工作表后在属性窗口修改(Name)字段），比如改成EquipmentSheet1、EquipmentSheet2，让代码更易读。

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