VBA: Using Macro to set sheet name from cell, but this won't work if cell is formula, can you help me?

[Jeester]

Basically, I am using:

[COLOR=#000000][FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)ActiveSheet.Name = Range("C2").ValueEnd Sub[/FONT][/COLOR]

To name my worksheet where:

C2='List of Students'!D5

In this instance the worksheet does not update this name when I update D5 in the other worksheet as C2 is a formula. Does anyone know of a fix for this?

Cheers

Jeester

 

[Michael M]

Change it to a worksheet _activate event.
When you then activate the sheet it will update the sheet name

 

[Jeester]

Thank you!
It works great! (Though only when I click on the sheet, if you know a way where it can change without having to open the worksheet whos name is changing that would be fantastic, but this is great really!)

I'm still pretty new at VBA (ie I started using it yesterday!).

 

[Michael M]

Another option would be to put this code in the "This Workbook" module
It will trigger when ANY worksheet is recalculated.
Change the number in red to whatever the Sheets code name is
The code name of the sheet is the one OUTSIDE the brackets, when you look at it in the VBE project Explorer

Private Sub Workbook_sheetcalculate(ByVal sh As Object)
Sheet1.Name = Sheet1.Range("C2").Value
End Sub