Worksheet Name from Cell Value

[Guest]

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

 

[Guest]

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

Regards,
Stefi



„Edcase†ezt írta:

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

 

[Guest]

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!

 

[Guest]

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]

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)

 

[Bob Phillips]

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)

 

[Guest]

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.

 

[Guest]

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]

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)

 

[Guest]

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]

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)