Changing Tab name real time by specifying in another page.

joelmathew

New Member
Joined
Mar 5, 2015
Messages
35
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
The code is in the wrong place. It should be with the Change event of your worksheet "dashboard":
Code:
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

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
Correction: the previous code would display a message when changing any other cell than L8.

Code:
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

New Member
Joined
Mar 5, 2015
Messages
35
The code is in the wrong place. It should be with the Change event of your worksheet "dashboard":
Code:
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

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
You're welcome, but please use the corrected code in post #3.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,335
Messages
5,595,573
Members
413,996
Latest member
mabelO

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top