MrExcel Publishing
Your One Stop for Excel Tips & Solutions

auto naming sheet


Posted by ;-) on February 01, 2002 6:31 AM

is there a way to make it so that a sheet's name will change automacaticaly according to a cell in the sheet?
ie. if cell A1 is 1 then the sheet name to chnage to 1, and if u type 2 in cell 2 , it will change to 2 straight away.


Posted by DK on February 01, 2002 6:37 AM

Only by using VBA. Try this:-

1. Right click on the worksheet tab and choose View Code.

2. In the code module that appears paste this:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
On Error Resume Next
Me.Name = Target
End If
End Sub

You need to include the on error in case the value entered in A1 is invalid (e.g. name exists, invalid characters such as /)

HTH,
D

Posted by JohnG on February 01, 2002 6:39 AM

Try this
Put this code in Thisworkbook
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Worksheets(1).Name = Range("A1").Value
End Sub

Posted by ;-) on February 01, 2002 7:02 AM

thx it worked, i got another Q, is it possible to make it so that if u enter a date in a1, then the sheet name will change to the day, ie. if 1/02/02 is enter in a1, then the sheet name will change to Saturday and so on..

thx for any help ;-)

Posted by DK on February 01, 2002 7:11 AM

Yep,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Not IsDate(Target) Then Exit Sub 'Not a valid date entered
Me.Name = Choose(WorksheetFunction.Weekday(Target), "Sunday", "Monday", "Tuesday", _
"Wednesday", "Thursday", "Friday")
End If
End Sub

Regards,
D