setting the sheet name as = to cell

[OneLooseCrank]

Hi all,


I would like the all of the sheet names in a workbook toequal to a cell within that sheet. The cell will always be N6. I found thisthread and followed the ‘answer’ that appears near to the top and saved as amacro enabled workbook. I am now working on this macro enabled workbook butnothing has happened. Of course, I changed the answer from “A1” to “N6”. I’ve not used VBA before so I’m not sure ifthere is a trick, such as closing the window the code was added…


The answer provided in the link is:
---

I'mguessing by this that you want the sheet name to change whenever you change thevalue in A1 on that sheet. To do this, you will need to use VB event code.Press ALT+F11 to go to the VB editor. On the left side of the window thatdisplays should be a panel entitled "Project - VBAProject" (if youdon't see it, press CTRL+R to make it appear). This panel display all thesheets in your project and the last item in the list is labeled"ThisWorkbook"... double-click that item and then copy/paste thefollowing code into the code window that opened up...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
Sh.Name = Sh.Range("A1").Value
End If
End Sub

That's it... go back to your worksheet and change the contents of A1 or, if youwant to keep what is in A1, just click in the Formula Bar and then hit theEnter key and the sheet name will change in response to your updating thecontents of A1.
---

Any additional would help would be greatly appreciated.
Thanks,

OLC

 

[footoo]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(0, 0) = "N6" Then ActiveSheet.Name = ActiveSheet.Range("N6")
End Sub

 

[steve the fish]

That code would work if you followed the procedure. Paste the code so we can take a look. Have you definately put the code into the correct 'ThisWorkbook' module?

 

[Jaafar Tribak]

Hi all,


I would like the all of the sheet names in a workbook toequal to a cell within that sheet. The cell will always be N6. I found thisthread and followed the ‘answer’ that appears near to the top and saved as amacro enabled workbook. I am now working on this macro enabled workbook butnothing has happened. Of course, I changed the answer from “A1” to “N6”. I’ve not used VBA before so I’m not sure ifthere is a trick, such as closing the window the code was added…


The answer provided in the link is:
---

I'mguessing by this that you want the sheet name to change whenever you change thevalue in A1 on that sheet. To do this, you will need to use VB event code.Press ALT+F11 to go to the VB editor. On the left side of the window thatdisplays should be a panel entitled "Project - VBAProject" (if youdon't see it, press CTRL+R to make it appear). This panel display all thesheets in your project and the last item in the list is labeled"ThisWorkbook"... double-click that item and then copy/paste thefollowing code into the code window that opened up...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
Sh.Name = Sh.Range("A1").Value
End If
End Sub

That's it... go back to your worksheet and change the contents of A1 or, if youwant to keep what is in A1, just click in the Formula Bar and then hit theEnter key and the sheet name will change in response to your updating thecontents of A1.
---

Any additional would help would be greatly appreciated.
Thanks,

OLC

Nothing will happen until you change\edit the content of cell N6.

Note that some characters are not accepted in worksheet names such as /\*? etc .. So the code will need some tweaking to validate user inputs.

 

[footoo]

Note that some characters are not accepted in worksheet names such as /\*? etc .. So the code will need some tweaking to validate user inputs.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Target.Address = "$N$6" Then ActiveSheet.Name = Target
If Err <> 0 Then MsgBox "Invalid sheet name."
End Sub

 

[OneLooseCrank]

Sorry for the long delay - thanks for the idea Jaafar, the cell N6 contained a special character which was stopping the sheet from accepting the name. Once removed the VBA worked perfectly. You def saved that one for me.
Cheers,
OLC