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