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

TIA

Sheila

Hi Sheila

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

--

XL2003
Regards

William



"Sheila" (remove underscores) wrote in message
...
Is there any way I can name a sheet from a worksheet cell??

TIA

Sheila

Thanks so much for that, I appreciate it.

Sheila



On Mon, 16 May 2005 04:25:47 +0100, "William"
wrote:

Hi Sheila

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

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

Thanks

Sheila




On Mon, 16 May 2005 04:25:47 +0100, "William"
wrote:

Hi Sheila

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

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.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"Sheila" (remove underscores) wrote in message
...
Is there any way I can name a sheet from a worksheet cell??

TIA

Sheila

Ummm, and where do i put this?

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

Sheila



On Mon, 16 May 2005 08:19:45 +0300, "Arvi Laanemets"
wrote:

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.

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<attarkon.ee
Arvi Laanemets



"Sheila" (remove underscores) wrote in message
...
Ummm, and where do i put this?

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

Sheila



On Mon, 16 May 2005 08:19:45 +0300, "Arvi Laanemets"
wrote:

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.

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

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

Think the OP wanted it the other way round, Arvi ! <g
i.e. name the sheet from an input into a certain cell ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----

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



On Mon, 16 May 2005 09:56:01 +0300, "Arvi Laanemets"
wrote:

Hi

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

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

PERFECT Max, thank you so much.

Sheila


On Mon, 16 May 2005 15:50:53 +0800, "Max"
wrote:

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

You're welcome, Sheila !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Sheila" (remove underscores) wrote in message
...
PERFECT Max, thank you so much.

Sheila