In your case, the value in that cell is really the string "10 Nov 08".

But if you had a real date in that cell and it was formatted to show "10 Nov
08", then the value would be the real date (11/10/2008 for me with my
settings)--not the string that you see.

So there's a difference between the .text property (what you see) and the .value
property (what you may see in the formula bar.

I can format a date (11/10/2008) to show November 10, 2008 in the cell. But the
..value is 11/10/2008.

The .value isn't a legal name (since it contains the slashes (with my USA
settings)).

The .Text is ok.

If I formatted the date to show:
Monday November 10, 2008
(with all those extra spaces)

Then that wouldn't be a valid worksheet name, either--since it's longer than 31
characters.



"Patrick C. Simonds" wrote:

And example of a value that appears in cell AB1 would be 10 Nov 08. That
is achieved by the the formula located in cell AB1 "=TEXT(B4,"dd mmm yy")".

What did you mean by "If you've already formatted the cells nice (and
legal)"? How should the cell be formatted?

"Dave Peterson" wrote in message
...
Another problem could be that the value in AB1 is a date. With my USA
settings,
that .value would be equal to something like:

11/12/2008

And worksheet names can not have slashes in them.

If you've already formatted the cells nice (and legal), you could use:

Sh.Name = Sh.Range(sStr).Text 'what appears in the cell, not the .value

or you could format it the way you like:

Sh.Name = format(Sh.Range(sStr).Value, "dd mmm yyyy") 'I like 4 digit
years!


"Patrick C. Simonds" wrote:

Can someone tell me why this code fails? It triggers the MsgBox. The
contents of cell AB1 is =TEXT(B4,"dd mmm yy"). Also what format should
be
given to cell AB1?

Sub Rename_Worksheets()
'
' Macro1 Macro
' Macro recorded 12/19/2005 by Cathy Baker
'

'

'This code runs to rename the worksheets

Dim wks As String
Dim Sh As Worksheet

wks = ActiveSheet.Name

Const sStr As String = "AB1"

On Error GoTo ErrHandler
For Each Sh In ThisWorkbook.Worksheets
Sh.Name = Sh.Range(sStr).Value
Next Sh

Worksheets(wks).Activate

Exit Sub
ErrHandler:
MsgBox "Cell" & sStr & "on sheet" & sh.Name & "is not valid sheet name"
Resume Next

End Sub

--

Dave Peterson

--

Dave Peterson