Changing name of sheet using VBA

[seh27]

I have a workbook with multiple sheets. Each sheet has a title (just words in a cell). Is there VBA formula that will have the sheet take the name of the title and also chnage when the title name changes? Thanks

 

[Jonmo1]

OK, so A1 changes via a formula.

We need to use the Calculate event instead of Change..
Try

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Name = Sh.Range("A1").Value
End Sub

Same instructions as before to install the code to the ThisWorkbook module.

 

[seh27]

I think I just don't know how to use this. I opened up macros, clicked this workbook, clicked create, deleted the sub and end sub that is automatically created, copied and pasted exactly what you sent me, and then exited out of the macros. Then I changed the cell it to see if it worked and nothing. Thank you for putting up with me...

 

[Jonmo1]

ok, when you click "ThisWorkbook"
Look at the title bar of the VBA window, it should say something like
"Micorosft Visual Basic for Applications - bookname.xls - [ThisWorkbook (Code)]

Does that say ThisWorkbook, or does it say some sheetname ?

Make sure to "Double Click" on ThisWorkbook, and check that the titlebar says ThisWorkbook

 

[seh27]

ok, when you click "ThisWorkbook"
Look at the title bar of the VBA window, it should say something like
"Micorosft Visual Basic for Applications - bookname.xls - [ThisWorkbook (Code)]

Does that say ThisWorkbook, or does it say some sheetname ?

Make sure to "Double Click" on ThisWorkbook, and check that the titlebar says ThisWorkbook

I did double click ThisWorkbook before hitting create but the title bar says
bookname.xsl - Module1 (Code)

 

[Jonmo1]

I don't follow what this means "before hitting create" ?

From the beginning...

open your excel workbook.
Right click on a Sheet's Tab and select "View Code" - this opens the VBA Window
Click View - Project Explorer
In the top left area you should see some project objects like sheets and modules
Find the object labled ThisWorkBook and double click on it
Or even better, Right Click on ThisWorkBook and select View Code
Now your title bar should say something like bookname.xsl - ThisWorkBook (Code)

Paste the code there