Worksheet name from Cell contents

  • Thread starter Thread starter Jason M
  • Start date Start date
J

Jason M

Is there a way to have the name of a worksheet reference a cell within the
worksheet?

In other words, if I have cell A1 contents of "Jay", is there a way for the
worksheet name to be "Jay" and then if I change cell A1 to "Jason" then the
worksheet name will automatically change to "Jason".

Thanks in advance!
 
Jason

You could use a Worksheet_Change() event and restrict it to A1 on the sheet

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1"), Target) Is Nothing Then
Me.Name = Range("A1").Value
End If
End Sub

Bear in mind there is no error checking for illegal characters, illegal
words or length.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Absolutely. I do this all the time with my documents. As a matter a
fact, I just did one today. Most of the time its with some text such
as "RPT1 - JAN 2005" but you can just use the cell if you prefer. With
"Jan 2005" in cell A1 on this first sheet in the workbook...

sub rename()
Dim TABNAME as string, CELLREF as string
CELLREF = sheets(1).range("A1").value
TABNAME = "RPT1 - " & CELLREF
SHEETS(1).NAME = TABNAME
end sub

If you just want to A1 as the sheet name, omit TABNAME string.

sub rename()
Dim CELLREF as string
CELLREF = sheets(1).range("A1").value
SHEETS(1).NAME = CELLREF
end sub

Good Luck
 
Hi JoJo,

This sounded so simple & seemed like an answer.......but it wasn't
Perhaps I have a different situation - thus:

We have an excel template which includes worksheets that :
1. are blank, ready to have data pasted into them by another Excel-based
program (3rd party) All information is "text" format.
2. use Excel functions to grab the data from these data sheets and perform
further calculations etc, including getting the "direction" value
3.use more Excel functions to produce report & charts, including the
"direction" value.

So what I believe is happening with the VBA code I've seen, is that it works
if the value is changed in the target cell, but not if that target cell
contains a formula, which just passes the "direction" value from other celles
or worksheets.

What I would like is for the worksheet name to change, for example,
dynamically...

Worksheet "N" Cell "G1" has "Northbound" pasted into it.
Worksheet "X-Data" Cell "L10" contains the formula "='N'!$G$1"
Worksheet "X-Report" Cell "A61" contains the formula "='X-Data'!$L$10"

so worksheet "X-Report" work change to "Northbound"

Will this work somehow? Please!!
 
Roger, Yes, your situation is different than Jason. I recommend
placing an IF statement in your formula cells (L10 and A61) if
possible. The If statement (see below) will only place the pasted
text, "Northbound" in the cell if it is NOT blank.

=IF(N!$G$1="","",N!$G$1)

Then in the VBA code add an if statement such as...

Dim TABNAME As String, CELLREF As String
CELLREF = Sheets(1).Range("G10").Value
TABNAME = CELLREF

If CELLREF = "" Then
Sheets(3).Name = "X-REPORT"
Else
Sheets(3).Name = TABNAME
End Sub

Let me know if this works for you. Good Luck.
JoJo
 
Hi JoJo,

I can see what you're trying, and maybe my implementation of the code you
suggested is wrong, thus:
===============
Private Sub Worksheet_Change(If Direction Populated)
Dim TABNAME As String, CELLREF As String
CELLREF = Sheets(2).Range("A61").Value
TABNAME = CELLREF
If CELLREF = "" Then
Sheets(2).Name = "X-REPORT"
Else
Sheets(2).Name = TABNAME
End If
End Sub
================
Sorry but I haven't programmed in VBA before
Sheets(2) is the worksheet where I implemented the cell if statement you
suggested (have used that heaps of times before !:^)} ) in cell A61, which
gets it value from Sheets(7) L10 (same If statement in that cell too)
IN the trial run these 2 cells were definitely populated with "Northbound".

Sorry to be a pain.....
 
Back
Top