Changing Tab name real time by specifying in another page.

[joelmathew]

hi All,

Thanks for reading this. i am a low macro user who wouldnt have a job without everyone here. so thank you!

I need a tabsheet to change its label every time when certian value ( L8 in the "dashboard" tab ) cell changes.

There are two tabs in the spreadsheet- "dashboard" and the sheet the name needs to change". changes made in L8 in the dashboard should automically change the name of 2nd sheet.

the 2nd sheet is called "PY 2018(19) risk details" ( yay the macro works) but it only works after i click ON the 2nd sheet.

I know the macro is working but i want it to work in Real time- Ie - i want it to change the tab name of the second sheet without me having to click it.

Can this be automated? the macro is currently pasted the in the "PY 2018(19) risk details tab" is


Private Sub Worksheet_Activate()
Call Worksheet_activate_part1

End Sub

Private Sub Workbook_SheetChange(ByVal Target As Range)
ActiveSheet.EnableCalculation = True
Call Worksheet_activate_part1
End Sub

Private Sub Worksheet_activate_part1()
Set Target = Worksheets("dashboard").Range("l8")
If Target = "" Then Exit Sub
On Error GoTo Badname
ActiveSheet.Name = Left(Target, 31)
Exit Sub
Badname:
MsgBox "Please revise the entry in L8." & Chr(13) _
& "It appears to contain one or more " & Chr(13) _
& "illegal characters." & Chr(13)
Range("A1").Activate
End Sub

 

[MarcelBeug]

The code is in the wrong place. It should be with the Change event of your worksheet "dashboard":

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count <> 1 Then Exit Sub
    
    If Not Intersect(Target, Range("L8")) Is Nothing Then
        On Error GoTo Badname
        Sheet2.Name = Left(Range("L8").Value, 31)
        On Error GoTo 0
        Exit Sub
    End If

Badname:
    MsgBox "Please revise the entry in L8." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
 

End Sub

 

[MarcelBeug]

Correction: the previous code would display a message when changing any other cell than L8.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count <> 1 Then Exit Sub
    
    If Intersect(Target, Range("L8")) Is Nothing Then Exit Sub

    On Error GoTo Badname
    Sheet2.Name = Left(Range("L8").Value, 31)
    On Error GoTo 0
    Exit Sub

Badname:
    MsgBox "Please revise the entry in L8." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
 

End Sub

 

[joelmathew]

The code is in the wrong place. It should be with the Change event of your worksheet "dashboard":

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count <> 1 Then Exit Sub
    
    If Not Intersect(Target, Range("L8")) Is Nothing Then
        On Error GoTo Badname
        Sheet2.Name = Left(Range("L8").Value, 31)
        On Error GoTo 0
        Exit Sub
    End If

Badname:
    MsgBox "Please revise the entry in L8." & Chr(13) _
    & "It appears to contain one or more " & Chr(13) _
    & "illegal characters." & Chr(13)
 

End Sub

many Many thanks. i couldnt have asked for more help Marcel.

 

[MarcelBeug]

You're welcome, but please use the corrected code in post #3.