Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Can't rename worksheet | Excel Discussion (Misc queries) | |||
Using information from one worksheet, to rename inserted worksheet | Excel Worksheet Functions | |||
why can't I rename my worksheet? | Excel Discussion (Misc queries) | |||
Rename the worksheet | Excel Discussion (Misc queries) | |||
rename worksheet | Excel Discussion (Misc queries) |