Rename tab on cell event

J

John G.

Looking for code that would simply, on the fly, depending what was entered in
a cell at anytime, any amount of times, the tab name would be renamed to what
the cell contents are.

I have seen some pretty elaborate ways of renaming tabs. I am new at Excel
and any help would be appreciated. Thanks!
 
M

Mike H

Hi,

Right click your sheet tab, view code and paste this in then every tiome A1
is changed to a 'legal' worksheet name the sheet will be renamed.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
ActiveSheet.Name = Range("A1").Value
enditall:
End Sub

Mike
 
J

John G.

Mike,

That works really slick. Question... If someone changes the cell to a
nothing or null value, it does not rename the sheet. Can there be a way to go
back to say "sheet 1" or "sheet 2" if cell becomes empty?

John G.
 
G

Gord Dibben

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
With Me
If .Range("A1") = "" Then
.Name = .Name
Else
.Name = .Range("A1").Value
End If
End With
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
J

John G.

Thanks Gord and Mike. Making life simpler, or complex, depends on what else
this leads to. Thanks a bunch!
 
J

John G.

Gord,
This returns the right value only the cell shows a square symbol between the
round number and the fraction. I tried to paste it here, but it did not come
over the same.
 
G

Gord Dibben

John

I think you have responded to the wrong posting.

No round numbers or fractions that I can see in my post.

But square symbols are usually linefeeds within a cell.


Gord
 
D

DKM

Great sub, however in MY case I need 4 sheets to be renamed based on cells in
sheet 1. Can anyone help?
 
D

DKM

Sorry, I should have been more specific.

I have a that has 8 sheets.

Sheet 1 is the master sheet.

All sheets have default names. (contractor1, contractor2....)

Once I enter the contractor name into the Master sheet (C4) I want sheet 2
to update to the value in sheet 1, C2. Similar for sheet 1, D2. And for
other cells in the master sheet.

thanks in advance.

dan
 
R

Robert McCurdy

In the sheet code module, paste this in - change [A2] to your desired cell.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A2]) Is Nothing _
Or [A2] = "" Then Exit Sub
Dim wks As Worksheet
For Each wks In Sheets
If UCase([A2]) = UCase(wks.Name) Then
MsgBox "Can't rename a sheet with " & [A2].Value _
& vbNewLine & "as that name already exist."
Exit Sub
End If
Next wks
On Error Resume Next
ActiveSheet.Name = [A2].Value2
If Err.Number <> 0 Then MsgBox Err.Description
End Sub

Well it was going to be a 1 liner, but that's been taken :)


Regards
Robert McCurdy
 
S

Scott

G’Day Gord

This works a treat and is simple to understand. I have one remaining
question.

The cell I am referencing (B1 in sheet 2) has a CONCATENATE function
(joining "sheet1!A1,A1" ) . When I change A1, I sheet2, B2 also changes as
does the Sheet2 sheet name. However, when I change the value in Sheet1!A1
there is no change to the Sheet2 sheet name, even though a B1 has changed? I
have tried "F9", even closing and opening the workbook but I can't seem to
force a change.

Is there a simple solution?

Thanks

Scott
 
G

Gord Dibben

You need a calculate event for that.

Private Sub Worksheet_Calculate()
On Error GoTo enditall
Application.EnableEvents = False
With Me
If .Range("B1") = "" Then
.Name = .Name
Else
.Name = .Range("B1").Value
End If
End With
enditall:
Application.EnableEvents = True
End Sub

Assumes code is in Sheet2 and B1 has formula

=CONCATENATE(Sheet1!A1,A1)


Gord
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top