VBA code to name a Sheet Name based upon inputted value in another sheet

[TheBigDeal]

Good afternoon!

I've got an excel sheet that I'm working on creating (draft attached). The idea is that a Class Advisor will type in the names/info for students 01 time on the first sheet (called ROSTER). From there, all information will push through to anywhere else in the workbook it's needed. The hurdle I'm running into is the code that changes the name of a sheet's tab to match a students inputted name on the roster sheet. At present, I only know how to make this work via stolen VBA code that specifies active sheet (So: To name SHEET7, I have to have the value entered somewhere on SHEET7).

So... My novice approach on the draft was to pull the inputted information on SHEET1 Cell A8 to SHEET7 Cell H3. That copies through via simple formula. Then, I use the VBA code to grab the value in cell H3 and rename the SHEET to match. The problem is, that doing it this way requires going to H3 of SHEET7 and manually clicking on H3 and pressing F2+Enter to update. That's a step I find ridiculous. Attempts to force a global re-caluculate/update via F9, or CTRL+ALT+F9, etc all fail to push the updated values. Literally the only way I've been able to get this code to work as intended is to select that cell, and F2+Enter.

Is there a way to change the VBA code so that it pulls directly from another sheet? Ideally, if each SHEET I want to rename has code pointing to values inserted on SHEET1 then the update would happen AS I actually enter the name. I'm just failing at getting the code to point back to SHEET1.

Help?

At present, the code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Specify the target cell whose entry shall be the sheet tab name.
If Target.Address <> "$H$3" Then Exit Sub
'If the target cell is empty (contents cleared) then do not change the shet name
If IsEmpty(Target) Then Exit Sub

'If the length of the target cell's entry is greater than 31 characters, disallow the entry.
If Len(Target.Value) > 31 Then
MsgBox "Worksheet tab names cannot be greater than 31 characters in length." & vbCrLf & _
"You entered " & Target.Value & ", which has " & Len(Target.Value) & " characters.", , "Keep it under 31 characters"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If

'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
'Verify that none of these characters are present in the cell's entry.
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(Target.Value, (IllegalCharacter(i))) > 0 Then
MsgBox "You used a character that violates sheet naming rules." & vbCrLf & vbCrLf & _
"Please re-enter a sheet name without the ''" & IllegalCharacter(i) & "'' character.", 48, "Not a possible sheet name !!"
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
Next i

'Verify that the proposed sheet name does not already exist in the workbook.
Dim strSheetName As String, wks As Worksheet, bln As Boolean
strSheetName = Trim(Target.Value)
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 the worksheet name does not already exist, name the active sheet as the target cell value.
'Otherwise, advise the user that duplicate sheet names are not allowed.
If bln = False Then
ActiveSheet.Name = strSheetName
Else
MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
"Please enter a unique name for this sheet."
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
End If

End Sub