Name Worksheet based on a cell value

[bluelabel]

Hi all,

I want to name a sheet based upon a cell value.

The cell value will be a date eg 20/03/2018

Cell B4 = 20/03/2018

Whenever the cell changes I would like the sheet name to change.

Is this possible please?

Thanks,

Blue

 

[My Aswer Is This]

How does the cell value change?
Does it change by you manually changing the date or as the result of a formula?

 

[My Aswer Is This]

Assuming the cell value changes do to a manual change try this.
This will not work if the cell value changes do to a formula change.

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-20-18 2:20 AM EDT
If Not Intersect(Target, Range("B4")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
ActiveSheet.Name = Format(Target.Value, "MM-DD-YY")
End If
Exit Sub
M:
MsgBox "That sheet name already exist or is a improper name"
End Sub

 

[bluelabel]

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-20-18 2:20 AM EDT
If Not Intersect(Target, Range("B4")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
ActiveSheet.Name = Format(Target.Value, "MM-DD-YY")
End If
Exit Sub
M:
MsgBox "That sheet name already exist or is a improper name"
End Sub
[/CODE]

Thanks, I tried this but when I run the code all I get is an empty macro dialogue which asks me which macro I want to run, but there's nothing there.

It is a manual entry too

 

[My Aswer Is This]

When you enter a date into Range("B4") then your active sheet name will be changed to the that date you entered.

So if you enter: 1/18/18
Your sheet name should change to 1-18-18

Do you have any other scripts in your sheet.
Try this script on a sheet with no other scripts in the sheet.
Are you using a PC or a Apple computer.
What version of Excel

 

[bluelabel]

Thank you I have it sorted.

2 things:
1. The code must be entered on the sheet you want to use it. Right click the sheet > View Code > paste code in
2. You don;t have to run the code. Just enter the data into the cell... in this case B4 and the sheet automagically changes.

Thanks for your help My Answer.

 

[My Aswer Is This]

Thank you I have it sorted.

2 things:
1. The code must be entered on the sheet you want to use it. Right click the sheet > View Code > paste code in
2. You don;t have to run the code. Just enter the data into the cell... in this case B4 and the sheet automagically changes.

Thanks for your help My Answer.

Not sure why your telling me what I already explained to you.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.