Check whether this event code fulfills your expectation
To Add Excel VBA Code to a Workbook / This Workbook Module
- Copy the code that you want to use
- Select the workbook in which you want to store the code
- Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
- In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
- Right-click on the ThisWorkbook object, and choose View Code
- Where the cursor is flashing, choose Edit | Paste
Code:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sWs As String, sCell As String
sWs = UCase(Sh.Name)
sCell = UCase(Sh.Range("A2").Value)
If Trim(sCell) <> "" Then
If Left(sWs, Len(sCell)) <> sCell Then
On Error Resume Next
Sh.Name = sCell & " " & sWs
On Error GoTo 0
End If
End If
End Sub