Auto Name Tab based on cell

[ryan_law2000]

Hey Everyone

I would like the tab at the bottom of the sheet to Automactially name = to cell C5
So if Cell C5 = "Ryan"
The tab at the bottom of the sheet will be named "Ryan"

Any ideas???

 

[VoG]

If you are changing C5 manually try this. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C5" Then Me.Name = Target.Value
End Sub

then press ALT + Q to return to your sheet.

 

[ryan_law2000]

Works perfect thanks!

 

[ryan_law2000]

Actually I am having a minor Problem here

In My "This workbook" I have a macro like this

Private Sub Workbook_Open()
If Sheets("Pay Pd").Range("C4").Value = "" _
Then Sheets("Pay Pd").Range("C4").Value = _
InputBox("Please Enter SAP Payroll #)

If this tab changes names (which it will as soon as the name is put in) the this macro is longer valid because its not named "Pay Pd" anymore... Can I change this code above to match the tab name no matte the name?

 

[lenze]

Use the Code Name. See my discussion here.
lenze

 

[VoG]

Check the code name - see example Fred code name is Sheet1

Then use

Private Sub Workbook_Open()
If Sheet1.Range("C4").Value = "" Then
    Sheet1.Range("C4").Value = InputBox("Please Enter SAP Payroll #")

Edit: posted 5 minutes too late!! Hiya lenze.

 

[ryan_law2000]

This has all been very helpful and works perfect!

One more question now though lol

Can I add text before it?

example if cell C4 = Ryan

I would like the tab to say "BOT - Ryan"

So I need it to have a "BOT -" Before the name entered

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C5" Then Me.Name = "BOT - " & Target.Value
End Sub

 

[lol.xls]

what about dates?

I've used:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C5" Then Me.Name = Target.Value
End Sub
And it works great if the cell input is formatted as text, but what about if you use a "daily report" and need the tabs to be named based off of a date entered into a cell?

 

[lenze]

Re: what about dates?

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address(False, False) = "C5" Then
    If IsDate(Target) Then
         Me.Name = Format(Target, "mm-dd-yy")
    Else: Me.Name = Target
    End If
End If
End Sub

lenze