Code will not run

P

Patrick C. Simonds

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
 
G

Gary Keramidas

what's in B4?

if AB1 equates to the same value on 2 sheets, you'll get an error because 2
sheets can't have the same name.
 
P

Patrick C. Simonds

I should add that if I type text into AB1 the code works. But I need to be
able to rename each sheet based on the date on the sheet.
 
B

Bob Phillips

The problem is that you are trying to activate a worksheet by name, having
changed the saved name. As you don't activate the sheet as you go through,
so you can ditch that bit altogether.

Sub Rename_Worksheets()
'This code runs to rename the worksheets

Dim Sh As Worksheet

Const sStr As String = "AB1"

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

Exit Sub
ErrHandler:
MsgBox "Cell " & sStr & ", " & Sh.Range(sStr).Text & ", on sheet " &
Sh.Name & " is not valid sheet name"
Resume Next

End Sub
 
D

Dave Peterson

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!
 
P

Patrick C. Simonds

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?
 
D

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top