Trying to update a sheet name based on a cell value. It works, sort of,

[DarrenKF]

Trying to update a sheet name based on a cell value. It works, sort of, but I have to go to that sheet and use the up or down arrows or some keystroke to get it to complete the function. Is there a way to automate this process so that I don't have to do anything except update the cell? My code is below. Please HELP!


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("E1")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub


Darren

[TMS]

Try:

Please Login or Register  to view this content.

[PrizeGotti]

Well that code is designed to specifically function when you click on the cell "E1".

The simplist way is to change the event to "Worksheet_Activate" and use this;

Please Login or Register  to view this content.

[DarrenKF]

That works much better, but I still have to click on the sheet then it changes. Sorry to be so picky and forgive me I am very new to VBA.

[PrizeGotti]

I would go with TMS over mine, I believe his should work without clicking anything.

[DarrenKF]

Unfortunately, TMS's code did not work. This is the code PrizeGotti sent and it functions well with the exception of having to click on the sheet to make it change

Private Sub Worksheet_Activate()
Set Target = Range("E1")
If Range("E1") = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
End Sub

[ByteMarks]

Are you using a formula to calculate the value in E1? If so, maybe use the sheet_calculate event

Please Login or Register  to view this content.

[DarrenKF]

OMG ! Genius. Thank you SO much ByteMarks