☰

在 Excel 中自动将工作表选项卡的名称与特定单元格值匹配

tag-icon • tag-icon microsoft-excel-2007
在 Excel 中自动将工作表选项卡的名称与特定单元格值匹配

如何自动将工作表选项卡的名称与特定单元格值匹配。换句话说，如果我更改该单元格的值，工作表选项卡的名称将自动更改为该值。

请逐步回答。

谢谢我的朋友们。

答案1

请参阅代码中的注释。此代码必须附加到工作表，其中单元格 A1 的条目将更改工作表的名称。为此，右键单击工作表的名称选项卡并选择View Code。复制代码并将其粘贴到代码窗格（代码编辑器右上区域的大窗格）。

要将名称更改条目的单元格从“A1”更改为“A2”，只需更改行中的单元格地址即可If Target.Address = "$A$1" Then。请确保包含美元符号。

Sub Worksheet_Change(ByVal Target As Range)
    'Worksheet change event occurs when a change is made
    'to a cell's value in this sheet

    'Target is the cell (or cells) in which values have been changed

    'Trigger worksheet name change only when a new name is 
    'entered in cell A1
    If Target.Address = "$A$1" Then

        'Don't change name if a formula is entered into cell A1
        'since worksheet change event does not trigger when
        'a formula is recalculated (in other words, name will not
        'change when the value of the formula changes
        If Not Target.HasFormula Then

            'Don't change name if no value in cell A1
            If Not Target.Value = vbNullString Then

                 'Do custom error handling in order to
                 'gracefully catch entry of invalid sheet names
                 'and other (less likely) errors
                 On Error GoTo ErrHandler

                 'The event will trigger only for the sheet to
                 'which the code for the change event is attached,
                 'so okay to refer to ActiveSheet
                 ActiveSheet.Name = Target.Value

            End If

        End If

    End If

    Exit Sub

ErrHandler:
    Warn user of error 
    MsgBox "Error " & Err & ":" & Error(Err)

    'Turn default error checking back on
    On Error GoTo 0

    'And end the sub
End Sub

相关内容