Making an entry in a cell change the name of a sheet

  • Thread starter Thread starter Mr. Bungle
  • Start date Start date
M

Mr. Bungle

Is anyone aware of a way to make data entered into a cell change the
name of the sheet? For example if you have a file with many sheets and
you'd like the name of the sheet to become the name of a certain date
that was entered into a particular cell. Many Thanks :-)
 
Mr. Bungle
The macro below will do what you want. Right-click on the sheet tab,
select View Code, and paste this macro into the module displayed. X-out to
return to the worksheet. As written, the cell with the date is A1 and the
date format for the subsequent sheet name is "mmm d yyyy". Change these as
needed. Note that I wrote in a check to validate that the entry made is a
valid date. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = "" Then Exit Sub
If Target.Address <> "$A$1" Then Exit Sub
If Not IsDate(Target) Then
MsgBox "The entry is not a valid date."
Application.EnableEvents = False
Target.ClearContents
Application.EnableEvents = True
Exit Sub
End If
ActiveSheet.Name = Format(Range("A1").Value, "mmm d yyyy")
End Sub
 
Back
Top