[dba-Tech] Excel Sheet Tab Naming routine

John Bartow jbartow at winhaven.net
Mon Apr 29 12:30:18 CDT 2019

Disregard this question. I decided to move this action to a Workbook routine instead. I'll post that code when I get it to the either the point of working or frustrating...

-----Original Message-----
From: dba-Tech <dba-tech-bounces at databaseadvisors.com> On Behalf Of John Bartow via dba-Tech
Sent: Sunday, April 28, 2019 11:03 PM
To: DBA-Tech (dba-tech at databaseadvisors.com) <dba-tech at databaseadvisors.com>
Cc: John Bartow <jbartow at winhaven.net>
Subject: [dba-Tech] Excel Sheet Tab Naming routine

Hi All,
I have a roster of names on the first sheet.
Each sheet after that copies the name from one line in the roster: =Roster!B3&" "&Roster!C3 into cell B1 of the sheet.
I'm trying to use the routine listed below to rename sheets based on the text of Cell B1.
This doesn't work, can someone spot why?

Private Sub Worksheet_Change(ByVal Target As Range) 'Specify the target cell whose entry shall be the sheet tab name.
If Target.Address <> "$B$1" Then Exit Sub 'If the target cell is empty (contents cleared) do not change the sheet name.
If IsEmpty(Target) Then Exit Sub

'Disallow the entry if it is greater than 31 characters.
If Len(Target.Value) > 31 Then
MsgBox "Worksheet names cannot be more than 31 characters." & vbCrLf & _ Target.Value & " has " & Len(Target.Value) & " characters.", _ 48, "Keep it under 31 characters."
Application.EnableEvents = False
Application.EnableEvents = True
Exit Sub
End If

'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
'Verify that none of these characters are present in the cell's entry.
Dim IllegalCharacter(1 To 7) As String, i As Integer
IllegalCharacter(1) = "/"
IllegalCharacter(2) = "\"
IllegalCharacter(3) = "["
IllegalCharacter(4) = "]"
IllegalCharacter(5) = "*"
IllegalCharacter(6) = "?"
IllegalCharacter(7) = ":"
For i = 1 To 7
If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then MsgBox "You used a character that violates sheet naming rules." & vbCrLf & _ "Enter a name without the ''" & IllegalCharacter(i) & "'' character.", _ 48, "Not a possible sheet name !!"
Application.EnableEvents = False
Application.EnableEvents = True
Exit Sub
End If
Next i

'Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean strSheetName = Trim(Target.Text) On Error Resume Next Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
bln = False
End If

'If the worksheet name does not already exist, name the sheet as cell value.
'Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then
ActiveSheet.Name = strSheetName
MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _ "Please enter a unique name for this sheet."
Application.EnableEvents = False
Application.EnableEvents = True
End If

End Sub

John B

dba-Tech mailing list
dba-Tech at databaseadvisors.com
Website: http://www.databaseadvisors.com

More information about the dba-Tech mailing list