Name worksheets after a cell value

Topic

New Reply

Hi all….I’m sure that I have seen a bunch of stuff written on this topic but I cannot find it in the forum…….I have a WB with 20-30 sheets, names Master, Master(2), Master(3) etc etc

I need a formula or worksheet change event VBA code that will change the sheet name and tab name to be whatever value is entered manually into cell N8……….thanks.

Reply | Quote

Replies

Not quite sure what you mean by sheet name and tab name.
The name on the tab is the sheet name.

You can use the Workbook SheetChange event for this.
It checks for changes to any sheet.
It does not check for changes to formulas though.

You have to remember with this, that there are restrictions

You have to check Worksheet and Chart names

Also check the entry for Blank, Invalid Characters, and also for being too long.

The example code below covers most of this.

You MUST have the option lines at the top.
One checks invalid variable typing and the other makes sure that the comparisons are not case sensitive

Code:

Option Compare Text
Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim wks As Worksheet, chs As Chart
Dim strName As String, fFound As Boolean
Dim strChar As String, intChar As Integer

If Target.Address = "$N$8" Then
    If Target  "" Then
        'Now Test for Invalid Character
        fFound = False
        For intChar = 1 To Len(Target)
            strChar = Mid(Target, intChar, 1)
            If InStr(":/?*[]", strChar)  0 Then
                fFound = True
                MsgBox "Sheet Name " & Target & " contains invalid Character " & strChar, vbExclamation
                Exit For
            End If
        Next
        If fFound = True Then Exit Sub
        'Then check length is not over 31
        If Len(Target) > 31 Then
            MsgBox "Cannot have a sheet Name more than 31 characters" & vbLf & "Name in cell is " & Len(Target), vbExclamation
            Exit Sub
        End If
        'Now check that this name is not used elsewhere
        fFound = False
        For Each wks In Worksheets
            If Target = wks.Name Then
                fFound = True
                strName = wks.Name
                MsgBox "Sheet " & strName & " already exists.", vbExclamation
                Exit For
            End If
        Next
        If fFound = True Then Exit Sub
        'Also check it is not a Chart sheet
        fFound = False
        For Each chs In Charts
            If Target = chs.Name Then
                fFound = True
                strName = chs.Name
                MsgBox "Chart Sheet " & strName & " already exists.", vbExclamation
                Exit For
            End If
        Next
        If fFound = True Then Exit Sub
        'If we get here then we can change the sheet name
        Sh.Name = Target
    End If
End If

End Sub

Reply | Quote

How about something like this?:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("n8")) Is Nothing Then
	On Error Resume Next
	ActiveSheet.Name = Range("n8")
  End If
End Sub

Reply | Quote

Andrew – a question…

In your code you have

If Target.Address = “$N$8” Then

I am curious why the IF since the user has already said he wanted N8 to contain the sheet name. Also, how would VB be able to determine if the Target.Address = “$N$8”?

Reply | Quote

The IF Target.Address=”$N$8″ is so the Code Block NOT get Processed if other cells are changed on the sheet
You don’t want it to happen if C5 is changed.
The Target is a parameter passed to the Private Sub By Excel.
It is the changed Cell
So if P9 was changed Excel would pass P9 as a Range Object

When we receive the Range called Target, we need to check that it was N8 that was altered

The $N$8 is because by default Address Property returns an Absolute Address

Reply | Quote

Got it. thank you.

Reply | Quote