Sheet Name change

[Barry Wright]

Got stumped ont hsi one :-( Trying to rename a sheet to what ever
value is in a certain cell. This returns an error, and the help files
did not help much,,,,

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim cRow As Long, cCol As Long
cRow = Target.Row
cCol = Target.Column
If (cCol = 14 And cRow = 5) Then ActiveSheet.Name = Target.Value

End Sub

 

[Don Guillett]

try

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address <> Cells(3, 5).Address Then Exit Sub
On Error Resume Next
ActiveSheet.Name = Target
End Sub

 

[Jim May]

looks like your are restricting any sheetname change to occur
only immediately after changing cell N5, or am I missing something here...

 

[Chip Pearson]

The code works fine for me. What exactly is the error message
you get?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Don Guillett]

for your cell use cells(5,14)

 

[David McRitchie]

Hi Barry,
Try this Event instead:

Private Sub Worksheet_Change(ByVal Target As Range)
If IsEmpty(Target) Then Exit Sub
If Target.Address(0, 0) = "N5" Then
On Error Resume Next
ActiveSheet.Name = Trim(Target.Text)
If Err.Number <> 0 Then
MsgBox Err.Number & " " & Err.Description
End If
End If
End Sub


if column an and row makes more sense
If target.row = 5 and target.column = 14 Then

Note use of .text instead of .valuje,
this should handle numbers with leading zeros and
dates and other data that don't have : \ / ? * [ or ]

More information on my sheets.htm and event.htm pages.

 

[KL]

Barry,
Your code works for me, but returns error (run-time error 1004) if cell N5
is empty or contains invalid characters. Also, your code cxan be simplified
(no need for variables) and you can add some error handling like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo errHandler
If Target = Cells(5, 14) Then ActiveSheet.Name = Target.Text
Exit Sub
errHandler:
MsgBox "Cell N5 contains an invalid sheet name!"
End Sub

Hope this helps,
KL

 

[David McRitchie]

Sorry change it back to a workbook event,
didn't realize it was for any sheet in the workbook,
I forgot to change add one additional line after
On Error GoTo 0
to put error handling back to normal.

 

[Barry Wright]

Ahh that hits the nail on the head! The problem is the invalid
characters. The N5 Cell is a date cell. I alow the date to be
entered in any format (5/5/05) but the cell is formated to be
displayed as May 05. (although the cell is displayed as May 05, the
True Value is 5/05/05, which of course cantains charaters not valid
for Sheet names. Any suggestions?

BTW

- The variables are needed, as I do ALOT of other stuff in this Sub,
not listed (Just simplified the code to focus on the error)

- The Blank Date Value is no biggie, the sheet name starts out named
"Week one" until some one enters a Date in Cell N5, and then changes
the sheet name. If N5 does not contain a valid date the it shoudl
change back to "Week one"

 

[Don Guillett]

I could tell you but apparently you didn't like the 1st response you got.

 

[KL]

Two things occur to me:

1) if the N5 value is always a date, then:

If Target = Cells(5, 14) Then ActiveSheet.Name = _
WorksheetFunction.Text(Target.Value,"mm-dd-yy")

2) if not, then you may want to check if it is a date:

If IsDate(Target) Then
ActiveSheet.Name = WorksheetFunction.Text(Target.Value, "mm-dd-yy")
Else
ActiveSheet.Name = Target.Text
End If

Regards,
KL

 

[KL]

Barry,

BTW the true value of May 5, 2005 is actually 38477. 5/5/05 like May 5 is
another way to display the date serial number.

KL

 

[Don Guillett]

or

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address <> Cells(3, 5).Address Then Exit Sub
On Error Resume Next
ActiveSheet.Name = Format(Target, "mm-dd-yyyy")
'ActiveSheet.Name = Target
End Sub

 

[David McRitchie]

Hi Barry,
I would suggest you include error notification such as I had
included, But in any case place the year before month before
day of month so that you can sort your worksheet names.
Best to show the errors such as if you try to rename a worksheet
to a name that already exists then you are warned rather than
just being ignored.

ActiveSheet.Name = format(Target.value,"yyyy-mm-dd")

 

[JE McGimpsey]

One way:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sDEFAULT As String = "Week one"
Const sFORMAT As String = "mmmm dd"
Const sNAMECELL As String = "N5"
Const sERROR As String = "Duplicate worksheet name in cell "
Dim sSheetName As String
sSheetName = sDEFAULT
With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
If IsDate(Range(sNAMECELL).Value) Then _
sSheetName = _
Format(Range(sNAMECELL).Value, sFORMAT)
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End With
End Sub

 

[Barry Wright]

Wow, thanks everyone for some much help on this. I tossed alround alot
fo your ideas, and here is what I think would best suits my needs.

- Erorr handling is done by Cell validation (Requires a Vaild Date or
a Blank Cell) I have an error message setup on the cell too.

Then only problem I have run into is when the cell is changed into a
date, it renames the cell just fine, but if they delte the cell value,
I want it to remain blank and the Sheet name to change back to the
Orignial Sheet names (listed in the Sub Below. (The ISBLANK is wrong
I think)



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim cRow As Long, cCol As Long
Dim CR As StatusType, NR As StatusType

cRow = Target.Row
cCol = Target.Column
If (cCol = 14 And cRow = 5) Then
If IsBlank(Target) Then
Select Case ActiveSheet.Index
Case 1
Set ActiveSheet.Name = "Week One"
Case 2
Set ActiveSheet.Name = "Week Two"
Case 3
Set ActiveSheet.Name = "Week Three"
Case 4
Set ActiveSheet.Name = "Week Four"
End Select
Else
ActiveSheet.Name = Format(Target, "mmm-dd")
End If
'Other Stuff Done here before Sub ends

End Sub

 

[JE McGimpsey]

One way:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim CR As StatusType, NR As StatusType

With Target
If .Address(False, False) = "N5" Then
If IsEmpty(.Value) Then
Sh.Name = "Week " & _
Choose(Sh.Index, "One", "Two", "Three", "Four")
Else
Sh.Name = Format(Target, "mmm-dd")
End If
End If
End With
'Other Stuff Done here before Sub ends
End Sub


I would still add some error checking after the attempted change. Even
with validation checking for a date or blank, if the user enters a date
that produces the name of an existing sheet, the macro will fail with a
run-time error.

 

[Barry Wright]

Ok, I agree with you now. You and all the others posting on this
link.....I need to check duplicate Sheet names. (Thanks again to
all thise who have help me out in this thread!) Although I may hav
enot choosen your code advice here, it have learned from ALL of it!


OK here is what I am going with....

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.Range)
Dim cRow As Long, cCol As Long
Dim CR As StatusType, NR As StatusType

With Target
If .Address(False, False) = "N5" Then
If Not Intersect(Target, Worksheets) = Nothing Then
MsgBox "There is already a Sheet with that Date, Please try
again."
Target.ClearContents
End If
If IsEmpty(.Value) Then
Sh.Name = "Week " & _
Choose(Sh.Index, " 1", " 2", " 3", " 4")
Else
Sh.Name = Format(Target, "mmm-dd")
End If
End If
End With

'Other Stuff Done here before Sub ends
End Sub


I think this is the ideal code flow:
1 > IF we are dealing with cell N5 Then
If Target is already a sheet name then
Show Error Message
Clear Target
If Target is empty then rename to default sheet name
Else Name sheet to target

I believe the above code will do this, except that My syntax of the
Intersect line is wrong...Can you help on that line? (What about the
Target.clearcontents....willl that work?

 

[JE McGimpsey]

Intersections work only with ranges, not sheets. But there are
workarounds. One way:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Const sDUP As String = _
"There is already a Sheet with that date, please try again."
Dim cRow As Long, cCol As Long
Dim wsTest As Worksheet
Dim sName As String
Dim bValid As Boolean
Dim CR As StatusType, NR As StatusType

With Target
If .Address(False, False) = "N5" Then
If Not IsEmpty(.Value) Then
If IsDate(.Value) Then
sName = Format(.Value, "mmm-dd")
bValid = True
For Each wsTest In Worksheets
If wsTest.Name = sName Then
MsgBox sDUP
.ClearContents
.Activate
bValid = False
Exit For
End If
Next wsTest
End If
End If
If Not bValid Then _
sName = "Week " & Choose(Sh.Index, "1", "2", "3", "4")
Sh.Name = sName
End If
End With
'Other Stuff Done here before Sub ends
End Sub

 

[David McRitchie]

Hi Barry,
It is usually easier and faster to just try to do something
and then catch the error with the on error condition, but
the main thing is that you are considering the not so remote
possibility of a duplicate sheet name.

Suggest you do NOT use mmm-dd format for the sheet names
because they won't sort in order if you sort the worksheets.
http://www.mvps.org/dmcritchie/excel/buildtoc.htm#sortallsheets

Suggest formatting as yyyy-mm-dd or a little shorter as yyyy_mmdd