Snake Eyes
Board Regular
- Joined
- Dec 14, 2010
- Messages
- 105
- Office Version
- 365
- 2016
- Platform
- Windows
The code below works as a macro to change the worksheet name based on the value of a cell.
The problem is that the name appears in all lower case.
What adjustment is necessary to display the name in all Upper Case?
The problem is that the name appears in all lower case.
What adjustment is necessary to display the name in all Upper Case?
VBA Code:
Sub Worksheet_Rename_Tab()
With Range("B6")
If Len(.Value) = 0 Or Len(.Value) > 31 Then Exit Sub
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(.Text, (IllegalCharacter(i))) > 0 Then
MsgBox "The formula in cell A1 returns a value containing a character that violates sheet naming rules." & vbCrLf & _
"Recalculate the formula without the ''" & IllegalCharacter(i) & "'' character.", _
48, "Not a possible sheet name !!"
Exit Sub
End If
Next i
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = (.Text)
On Error Resume Next
Set wks = ActiveWorkbook.Worksheets(strSheetName)
On Error Resume Next
If Not wks Is Nothing Then
bln = True
Else
bln = False
Err.Clear
End If
If bln = False Then
ActiveSheet.Name = strSheetName
ElseIf ActiveSheet.Name <> .Text Then
MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
"Recalculate the formula in cell A1 to return a unique name."
End If
End With
End Sub