Worksheet Name from Cell Value
 

I am building an excel template that uses a sheet as an entry form and
produces several sheets from the information entered. I want the worksheet
names to come from information entered into specific cells on the first sheet.

Many Thanks

Worksheet Name from Cell Value
 

ActiveSheet.Name = Range("A1") '<--CHANGE sheet and cell reference as
required!

Regards,
Stefi



€žEdcase€� ezt Ã*rta:

I am building an excel template that uses a sheet as an entry form and
produces several sheets from the information entered. I want the worksheet
names to come from information entered into specific cells on the first sheet.

Many Thanks

Worksheet Name from Cell Value
 

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
I am building an excel template that uses a sheet as an entry form and
produces several sheets from the information entered. I want the worksheet
names to come from information entered into specific cells on the first
sheet.

Many Thanks

Worksheet Name from Cell Value
 

Thanks for the code , but it seems not to work. I copied the code straight
from your post and followed the instructions, saved the workbook and
alteredvalue in the cell and nothing. What could I be doing wrong?



"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
I am building an excel template that uses a sheet as an entry form and
produces several sheets from the information entered. I want the worksheet
names to come from information entered into specific cells on the first
sheet.

Many Thanks

Worksheet Name from Cell Value
 

I've just tried it again and it works fine for me.

Put this line

MsgBox "WS Change event"

try again and see if it returns the MsgBox

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
Thanks for the code , but it seems not to work. I copied the code straight
from your post and followed the instructions, saved the workbook and
alteredvalue in the cell and nothing. What could I be doing wrong?



"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
I am building an excel template that uses a sheet as an entry form and
produces several sheets from the information entered. I want the
worksheet
names to come from information entered into specific cells on the
first
sheet.

Many Thanks

Worksheet Name from Cell Value
 

Bob...I am trying to do the same thing. The problem is when I enter in a
cell in a different sheet. For instance, I need the sheet name on sheet 3 to
be named what I enter in cell F2 on sheet 1. I can link a cell in sheet 3 to
the entry cell on sheet 1, however, I need to edit and enter the cell on
sheet 3 to get the sheet name to change. Bottom line: I want to enter a
descriptor on an entry sheet and have a specific sheet change name to what
was entered. Is this possible? Thanks!

"Bob Phillips" wrote:

I've just tried it again and it works fine for me.

Put this line

MsgBox "WS Change event"

try again and see if it returns the MsgBox

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
Thanks for the code , but it seems not to work. I copied the code straight
from your post and followed the instructions, saved the workbook and
alteredvalue in the cell and nothing. What could I be doing wrong?



"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
I am building an excel template that uses a sheet as an entry form and
produces several sheets from the information entered. I want the
worksheet
names to come from information entered into specific cells on the
first
sheet.

Many Thanks

Worksheet Name from Cell Value
 

How would you know which cell refers to which sheet? You need some linkage
for the code to base upon. I suppose you could always use, first sheet takes
A2, second takes B2, etc., but it needs user discipline not to move the
sheets around.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Deeds" wrote in message
...
Bob...I am trying to do the same thing. The problem is when I enter in a
cell in a different sheet. For instance, I need the sheet name on sheet 3
to
be named what I enter in cell F2 on sheet 1. I can link a cell in sheet 3
to
the entry cell on sheet 1, however, I need to edit and enter the cell on
sheet 3 to get the sheet name to change. Bottom line: I want to enter a
descriptor on an entry sheet and have a specific sheet change name to what
was entered. Is this possible? Thanks!

"Bob Phillips" wrote:

I've just tried it again and it works fine for me.

Put this line

MsgBox "WS Change event"

try again and see if it returns the MsgBox

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
Thanks for the code , but it seems not to work. I copied the code
straight
from your post and followed the instructions, saved the workbook and
alteredvalue in the cell and nothing. What could I be doing wrong?



"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
I am building an excel template that uses a sheet as an entry form
and
produces several sheets from the information entered. I want the
worksheet
names to come from information entered into specific cells on the
first
sheet.

Many Thanks

Worksheet Name from Cell Value
 

Thanks Bob...how about this. In Sheet 1 Cell A10 I enter "Sales"....On Sheet
5 cell F10 there is a link to Sheet 1 A10. So, now I have the name I want
for the sheet in cell F10 on Sheet5. The problem arises in that it does not
change the sheet name ...unless I edit and enter that cell(F10) (keystroke F2
and enter). Is there anyway to do this? Thanks again.

"Bob Phillips" wrote:

How would you know which cell refers to which sheet? You need some linkage
for the code to base upon. I suppose you could always use, first sheet takes
A2, second takes B2, etc., but it needs user discipline not to move the
sheets around.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Deeds" wrote in message
...
Bob...I am trying to do the same thing. The problem is when I enter in a
cell in a different sheet. For instance, I need the sheet name on sheet 3
to
be named what I enter in cell F2 on sheet 1. I can link a cell in sheet 3
to
the entry cell on sheet 1, however, I need to edit and enter the cell on
sheet 3 to get the sheet name to change. Bottom line: I want to enter a
descriptor on an entry sheet and have a specific sheet change name to what
was entered. Is this possible? Thanks!

"Bob Phillips" wrote:

I've just tried it again and it works fine for me.

Put this line

MsgBox "WS Change event"

try again and see if it returns the MsgBox

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
Thanks for the code , but it seems not to work. I copied the code
straight
from your post and followed the instructions, saved the workbook and
alteredvalue in the cell and nothing. What could I be doing wrong?



"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
I am building an excel template that uses a sheet as an entry form
and
produces several sheets from the information entered. I want the
worksheet
names to come from information entered into specific cells on the
first
sheet.

Many Thanks

Worksheet Name from Cell Value
 

This works


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Name = Sh.Range("F10").ValueEnd Sub

You might want to add a test to ignore certain sheets


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name < "Sheet1" Then
Sh.Name = Sh.Range("F10").Value
End If
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Deeds" wrote in message
...
Thanks Bob...how about this. In Sheet 1 Cell A10 I enter "Sales"....On
Sheet
5 cell F10 there is a link to Sheet 1 A10. So, now I have the name I want
for the sheet in cell F10 on Sheet5. The problem arises in that it does
not
change the sheet name ...unless I edit and enter that cell(F10) (keystroke
F2
and enter). Is there anyway to do this? Thanks again.

"Bob Phillips" wrote:

How would you know which cell refers to which sheet? You need some
linkage
for the code to base upon. I suppose you could always use, first sheet
takes
A2, second takes B2, etc., but it needs user discipline not to move the
sheets around.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Deeds" wrote in message
...
Bob...I am trying to do the same thing. The problem is when I enter
in a
cell in a different sheet. For instance, I need the sheet name on
sheet 3
to
be named what I enter in cell F2 on sheet 1. I can link a cell in
sheet 3
to
the entry cell on sheet 1, however, I need to edit and enter the cell
on
sheet 3 to get the sheet name to change. Bottom line: I want to
enter a
descriptor on an entry sheet and have a specific sheet change name to
what
was entered. Is this possible? Thanks!

"Bob Phillips" wrote:

I've just tried it again and it works fine for me.

Put this line

MsgBox "WS Change event"

try again and see if it returns the MsgBox

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
Thanks for the code , but it seems not to work. I copied the code
straight
from your post and followed the instructions, saved the workbook
and
alteredvalue in the cell and nothing. What could I be doing wrong?



"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Edcase" wrote in message
...
I am building an excel template that uses a sheet as an entry
form
and
produces several sheets from the information entered. I want
the
worksheet
names to come from information entered into specific cells on
the
first
sheet.

Many Thanks

Worksheet Name from Cell Value
 

Thanks Bob....this works great! One more curve for you. Can I get specific
on what cell in Sheet1? Let's say there are 10 cells that I want to enter on
Sheet1 which will rename 10 different sheets. Can we do that?
Thanks again...you guys are lifesavers!

"Bob Phillips" wrote:

This works


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Name = Sh.Range("F10").ValueEnd Sub

You might want to add a test to ignore certain sheets


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name < "Sheet1" Then
Sh.Name = Sh.Range("F10").Value
End If
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Deeds" wrote in message
...
Thanks Bob...how about this. In Sheet 1 Cell A10 I enter "Sales"....On
Sheet
5 cell F10 there is a link to Sheet 1 A10. So, now I have the name I want
for the sheet in cell F10 on Sheet5. The problem arises in that it does
not
change the sheet name ...unless I edit and enter that cell(F10) (keystroke
F2
and enter). Is there anyway to do this? Thanks again.

"Bob Phillips" wrote:

How would you know which cell refers to which sheet? You need some
linkage
for the code to base upon. I suppose you could always use, first sheet
takes
A2, second takes B2, etc., but it needs user discipline not to move the
sheets around.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Deeds" wrote in message
...
Bob...I am trying to do the same thing. The problem is when I enter
in a
cell in a different sheet. For instance, I need the sheet name on
sheet 3
to
be named what I enter in cell F2 on sheet 1. I can link a cell in
sheet 3
to
the entry cell on sheet 1, however, I need to edit and enter the cell
on
sheet 3 to get the sheet name to change. Bottom line: I want to
enter a
descriptor on an entry sheet and have a specific sheet change name to
what
was entered. Is this possible? Thanks!

"Bob Phillips" wrote:

I've just tried it again and it works fine for me.

Put this line

MsgBox "WS Change event"

try again and see if it returns the MsgBox

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edcase" wrote in message
...
Thanks for the code , but it seems not to work. I copied the code
straight
from your post and followed the instructions, saved the workbook
and
alteredvalue in the cell and nothing. What could I be doing wrong?



"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Edcase" wrote in message
...
I am building an excel template that uses a sheet as an entry
form
and
produces several sheets from the information entered. I want
the
worksheet
names to come from information entered into specific cells on
the
first
sheet.

Many Thanks

Worksheet Name from Cell Value
 

Deeds,

It doesn't matter what cells on Sheet1 that you use, the constraint is it
must be F10 on the other sheets. So you can link Sheet2!F10 to A5 on Sheet1,
Sheet3!F10 to B11, and so on. It should not matter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"deeds" wrote in message
...
Thanks Bob....this works great! One more curve for you. Can I get
specific
on what cell in Sheet1? Let's say there are 10 cells that I want to enter
on
Sheet1 which will rename 10 different sheets. Can we do that?
Thanks again...you guys are lifesavers!

"Bob Phillips" wrote:

This works


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Sh.Name = Sh.Range("F10").ValueEnd Sub

You might want to add a test to ignore certain sheets


Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
If Sh.Name < "Sheet1" Then
Sh.Name = Sh.Range("F10").Value
End If
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Deeds" wrote in message
...
Thanks Bob...how about this. In Sheet 1 Cell A10 I enter
"Sales"....On
Sheet
5 cell F10 there is a link to Sheet 1 A10. So, now I have the name I
want
for the sheet in cell F10 on Sheet5. The problem arises in that it
does
not
change the sheet name ...unless I edit and enter that cell(F10)
(keystroke
F2
and enter). Is there anyway to do this? Thanks again.

"Bob Phillips" wrote:

How would you know which cell refers to which sheet? You need some
linkage
for the code to base upon. I suppose you could always use, first
sheet
takes
A2, second takes B2, etc., but it needs user discipline not to move
the
sheets around.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Deeds" wrote in message
...
Bob...I am trying to do the same thing. The problem is when I
enter
in a
cell in a different sheet. For instance, I need the sheet name on
sheet 3
to
be named what I enter in cell F2 on sheet 1. I can link a cell in
sheet 3
to
the entry cell on sheet 1, however, I need to edit and enter the
cell
on
sheet 3 to get the sheet name to change. Bottom line: I want to
enter a
descriptor on an entry sheet and have a specific sheet change name
to
what
was entered. Is this possible? Thanks!

"Bob Phillips" wrote:

I've just tried it again and it works fine for me.

Put this line

MsgBox "WS Change event"

try again and see if it returns the MsgBox

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Edcase" wrote in message
...
Thanks for the code , but it seems not to work. I copied the
code
straight
from your post and followed the instructions, saved the
workbook
and
alteredvalue in the cell and nothing. What could I be doing
wrong?



"Bob Phillips" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1" '<===== Change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing
Then
Me.Name = Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to
be
'placed in the appropriate worksheet code module, not a
standard
'code module. To do this, right-click on the sheet tab,
select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing
direct)

"Edcase" wrote in message
...
I am building an excel template that uses a sheet as an
entry
form
and
produces several sheets from the information entered. I
want
the
worksheet
names to come from information entered into specific cells
on
the
first
sheet.

Many Thanks