Hello everyone,
I found the following code elsewhere on the net, and have attempted to use it in my workbook to change the names of all 31 worksheets when a certain cell's (C7) value changes. The code looks good to me, so I cannot understand why it is not working. I am not getting error messages, just no changes to the sheet names.
I believe this may be due to the value of C7 on each sheet being dictated by a formula, so the value is not being changed with the sheet active, but indirectly with a formula that pulls the value from a cell on another sheet, and then adds.
(i.e. =TEXT('Populator Tools'!$C$25+1,"mm-dd-yy") )
Am I completely off base with this assumption, or on the right track; and if I am on the right track how do I fix this so it works?
Any advice will be helpful.
Code Below.
Thanks in Advance!
The Workbook_SheetChange Code Stored in This Workbook Module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim sNewName As String
Dim sMsg As String, sEndMsg As String
Dim sTitle As String
Const sDATEFORM As String = "mm-dd-yy"
Const sNUMFORM As String = "#0.00"
'Set the title for the message box
sTitle = "Invalid Sheet Name"
'Make sure it's the cell we want
If Target.Address = "$C$7" Then
'Account for specific data in the cell
If IsDate(Target.Value) Then
sNewName = Format(Target.Value, sDATEFORM)
ElseIf IsNumeric(Target.Value) Then
sNewName = Format(Target.Value, sNUMFORM)
Else
'sNewName = Target.Value
'thanks, Charlie
sNewName = CStr(Target.Value)
End If
'Get rid of illegal or unwanted characters
sNewName = CleanSheetName(sNewName)
'Create the end of the prompt for the message box
sEndMsg = vbNewLine & vbNewLine & "The sheet name will not be changed." & _
vbNewLine & vbNewLine & "Sheet name attempted: " & sNewName
'Establish error checking
On Error Resume Next
'Attempt to rename the sheet
Sh.Name = sNewName
'If there's an error
If Err.Number <> 0 Then
'Be more descriptive for a certain error, otherwise
'return the error that Excel returns
If Left(Err.Description, 19) = "Application-defined" Then
sMsg = "You entered an invalid sheet name." & sEndMsg
Else
sMsg = Err.Description & sEndMsg
End If
'Display the error
MsgBox sMsg, vbOKOnly, sTitle
End If
On Error GoTo 0
End If
End Sub
Illegal Character Checker/Cleaner - Stored in Module 1
Public Function CleanSheetName(ByVal sOldName As String, _
Optional sReplacement As String = "_") As String
Dim vaIllegal As Variant
Dim i As Long
Dim sTemp As String
sTemp = sOldName
'List unwanted characters
vaIllegal = Array(".", "?", "!", "*", "/", "", "[", "]", "'", ":")
'Make sure replacement isn't illegal
For i = LBound(vaIllegal) To UBound(vaIllegal)
If sReplacement = vaIllegal(i) Then
sReplacement = "-"
Exit For
End If
Next i
'Replace all illegals with the replacement
For i = LBound(vaIllegal) To UBound(vaIllegal)
sTemp = Replace(sTemp, vaIllegal(i), sReplacement)
Next i
CleanSheetName = sTemp
End Function
Bookmarks