Sheet Name

[Sheila]

Is there any way I can name a sheet from a worksheet cell??

TIA

Sheila

 

[William]

Hi Sheila

Sub test()
Sheets("Sheet1").Name = Sheets("Sheet1").Range("A1")
End Sub

--

XL2003
Regards

William
(e-mail address removed)

 

[Sheila]

Thanks so much for that, I appreciate it.

Sheila

 

[Sheila]

Ummm, ok, but where do I put this? I answered thanks before I ead
this properly.

Thanks

Sheila

 

[Arvi Laanemets]

Hi

=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;35)

The workbook must be saved, i.e. the formula doesn't work in freschly
created worlbook, until it is saved.
To refer to another sheet, replace A1 in formula with SheetName!A1.

 

[Sheila]

Ummm, and where do i put this?

(i know, pain in the neck to be blonde)

Sheila

 

[Arvi Laanemets]

Hi

Into cell where you want the sheet name to be displayed.

P.S. probably you have to replace semicolons in formula with commas!

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets

Ummm, and where do i put this?

(i know, pain in the neck to be blonde)

Sheila

Hi

=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;35)

The workbook must be saved, i.e. the formula doesn't work in freschly
created worlbook, until it is saved.
To refer to another sheet, replace A1 in formula with SheetName!A1.

 

[Max]

Perhaps you'd also like to try this recent post by Jason Morin ..
---------
Assuming your target cell for the sheet name is B1, right-click on the
worksheet tab, select View Code, and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Me.[B1], Target) Is Nothing Then
On Error GoTo InvalidName
Me.Name = Target.Value
Exit Sub
End If
InvalidName:
MsgBox "Invalid sheet name."
End Sub

---
Press ALT+Q to close the VBE.
---
Back in Excel, test it out by inputting the desired sheetname, say: Sheila
into cell B1. When you press ENTER, the sheetname will change to: Sheila

Here's a sample file with the implemented code :
http://flypicture.com/p.cfm?id=48813
(Right-click on the link: "Download File" at the top in the page, just above
the ads)
File: Sheila_wksht.xls

 

[Max]

Think the OP wanted it the other way round, Arvi ! <g>
i.e. name the sheet from an input into a certain cell ..

 

[Sheila]

Sorry, I must not have explained myself well. I want to be able to
name the worksheet from the contents of a particular cell on that
worksheet, not p-ut the worksheet name into a cell.

Sheila

 

[Sheila]

PERFECT Max, thank you so much.

Sheila

 

[Max]

You're welcome, Sheila !
Thanks for the feedback ..