Rename worksheet tab as date not text
 

Hello
I copied the code below from a book and it only works with text. How can
I make this work with a cell formatted as a date

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.Name = .Range("J1").Value '.text ???
End With
End Sub
This is what I have
JI is a merged cell from Ji to L1-
also this the formula in JI
=Sep307!J1+IF(WEEKDAY(Sep307!J1+1)=7,3,4)

Thanks

Rename worksheet tab as date not text
 

You cannot have slashes ( /) in a worksheet name
Workaround is to use hyphens, as in 09-10-07 for today

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
newname = Application.WorksheetFunction.Text(Range("J1"), "mm-dd-yy")
ActiveSheet.Name = newname
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Wanna Learn" wrote in message
...
Hello
I copied the code below from a book and it only works with text. How
can
I make this work with a cell formatted as a date

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.Name = .Range("J1").Value '.text ???
End With
End Sub
This is what I have
JI is a merged cell from Ji to L1-
also this the formula in JI
=Sep307!J1+IF(WEEKDAY(Sep307!J1+1)=7,3,4)

Thanks

Rename worksheet tab as date not text
 

I presume that this sheet is not sheet Sep307 and that you are getting a date
displayed in J1 and not some error ( I got confused somewhat between J1, JI
and Ji).
Then back up in your With statement, use the .Text parameter instead of
..Value, as:
With ActiveSheet
.Name = Range("J1").Text
End With

or more simply, since you're only changing the one ActiveSheet property,
replace all 3 of those lines with:
ActiveSheet.Name = Range("J1").Text

It works for me as long as there's something in J1. I actually wrote it all
as:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not IsEmpty(Range("J1")) Then
ActiveSheet.Name = Range("J1").Text
End If
End Sub

Make sure the code is in your worksheet code module, not in a general code
module. Right-click on the sheet's name tab and choose View Code from the
list - the code should be in the code module presented to you at that time.

"Wanna Learn" wrote:

Hello
I copied the code below from a book and it only works with text. How can
I make this work with a cell formatted as a date

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.Name = .Range("J1").Value '.text ???
End With
End Sub
This is what I have
JI is a merged cell from Ji to L1-
also this the formula in JI
=Sep307!J1+IF(WEEKDAY(Sep307!J1+1)=7,3,4)

Thanks

Rename worksheet tab as date not text
 

Thank you Bernard Liengme and JLatham ... both solutions work fine

"JLatham" wrote:

I presume that this sheet is not sheet Sep307 and that you are getting a date
displayed in J1 and not some error ( I got confused somewhat between J1, JI
and Ji).
Then back up in your With statement, use the .Text parameter instead of
.Value, as:
With ActiveSheet
.Name = Range("J1").Text
End With

or more simply, since you're only changing the one ActiveSheet property,
replace all 3 of those lines with:
ActiveSheet.Name = Range("J1").Text

It works for me as long as there's something in J1. I actually wrote it all
as:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not IsEmpty(Range("J1")) Then
ActiveSheet.Name = Range("J1").Text
End If
End Sub

Make sure the code is in your worksheet code module, not in a general code
module. Right-click on the sheet's name tab and choose View Code from the
list - the code should be in the code module presented to you at that time.

"Wanna Learn" wrote:

Hello
I copied the code below from a book and it only works with text. How can
I make this work with a cell formatted as a date

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.Name = .Range("J1").Value '.text ???
End With
End Sub
This is what I have
JI is a merged cell from Ji to L1-
also this the formula in JI
=Sep307!J1+IF(WEEKDAY(Sep307!J1+1)=7,3,4)

Thanks