VBA Tab Name Change

[SteveWr]

I have a small macro for changing the tab name of a separate worksheet when the value is changed in a specific cell on another sheet called Summary. It works okay but I cant see how to get the tab name to change automatically when the value of the cell C7 changes on the Summary sheet.

Sheet 2 does change when I click on any cell on that page. Can someone please advise?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Sheet2 As Worksheet
Set Sheet2 = ActiveWorkbook.ActiveSheet
Sheet2.Name = "" & Worksheets("Summary").Range("C7").Value
End Sub

 

[BSALV]

this in the sheetmodule of Summary is enough, i suppose.
It's converted to a string, in case you want to name the sheet with a number and sheets(2)<>sheets("2")

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = "$C$7" Then ThisWorkbook.Sheet2.Name = CStr(Target.Value)
End Sub

 

[SteveWr]

this in the sheetmodule of Summary is enough, i suppose.
It's converted to a string, in case you want to name the sheet with a number and sheets(2)<>sheets("2")

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = "$C$7" Then ThisWorkbook.Sheet2.Name = CStr(Target.Value)
End Sub

Thank you for your reply however I get an error - Compile error: Method or data member not found when I execute this

 

[BSALV]

try it without "ThisWorkbook."

Sheet2.Name = CStr(Target.Value)

 

[SteveWr]

try it without "ThisWorkbook."

Sheet2.Name = CStr(Target.Value)

Brilliant thank you that works exactly as I wanted, thank you for your help.