Excel tabs that are driven by cell values

Cirby

New Member
Joined
May 9, 2002
Messages
17
I'm using Excel 2003 and would like to have the tabs of each worksheet named by whatever is entered into cell C9 of the worksheet. I'm not sure if this will matter, but I've merged C9...E9. I'm if this is a repetitive question, I've searched the archives and am not finding the answer. Any guidance is appreciated.
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this in the Workbook SheetChange event:

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo Invalid
If Target.Address = "$C$9" Then
    Sh.Name = Target
    Exit Sub
End If
Invalid:
MsgBox "Sheet name invalid", vbExclamation
End Sub
 

Cirby

New Member
Joined
May 9, 2002
Messages
17
I'm relatively inexperienced with this, so I apologize if you've already received this message. I gave your code a shot and it didn't work. Thanks for trying. I'm open to any other suggestions. Thanks again for the quick response.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I also replied to your PM with this:

Right click on one of your sheet tabs. Click on View Code. On the left side of your screen, under VBA project (for your workbook) you will see that sheet selected, click on the ThisWorkbook in that same section, paste the code into the white area on the right.
Hit Alt-q.

When you save the workbook, this code will be saved with it.

If this doesn't do what you want, you will need to be more specific in what is not working
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Revised code:

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo Invalid
If Target.Address = "$C$9" Then Sh.Name = Target
Exit Sub
Invalid:
MsgBox "Sheet name invalid", vbExclamation
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,503
Members
412,671
Latest member
xcihan
Top