Automatically Change Sheet Tab Name With Date in Cell

[chrisneu]

Hello! I am trying to automate naming all my tabs with the date entered into cell A2 in every worksheet, but with a different date format. The date format of the A2 is [$-F800]dddd, mmmm dd, yyyy (or Friday, April 01, 2011). But I want the tab to be named "Apr-01 Fri". Here is what I tried: to convert the formats, I decided to use the next located cell B2 with a different date format, white-on-white text and referencing it to =A2 with the code below. It works perfectly fine with word-strings in B2, but when entering a date, it gives me my code's error of invalid sheet name. My guess is that the date-format should be converted within code, not in the cell. Thank you for any help!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strNew As String, strOld As String
    If Target.Cells.Count > 1 Then Exit Sub
    
    With Range("B2")
        On Error Resume Next
        If Not (Application.Intersect(Target, .DirectPrecedents) Is Nothing) Then
            strOld = Me.Name
            strNew = CStr(.Value)
            Me.Name = strNew
          
            If Me.Name = strOld Then
                 MsgBox "The name " & strNew & " is not a valid  sheet name"
            End If
        End If
        On Error GoTo 0
    End With
    
End Sub