This site will look better if you upgrade to a browser that supports web standards.

Sheetname from cell, automatically

If you want to have your sheet name change when a cell value is changed, you can use this Worksheet_Change() event macro. Put it in your worksheet code module. Note that it has minimal error checking.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        Const sNAMECELL As String = "A1"
        Const sERROR As String = "Invalid worksheet name in cell "
        Dim sSheetName As String
        
        With Target
            If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
                sSheetName = Range(sNAMECELL).Value
                If Not sSheetName = "" Then
                    On Error Resume Next
                    Me.Name = sSheetName
                    On Error GoTo 0
                    If Not sSheetName = Me.Name Then _
                        MsgBox sERROR & sNAMECELL
                End If
            End If
        End With
    End Sub

Change the value of sNAMECELL to your desired cell.

This page last updated Sunday, 28 November 2004

McGimpsey & Associates

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

Sheetname from cell, automatically

Quick Links