Solved How can I link a cell to a tab name?

May 16, 2011 at 06:48:38
Specs: Window's 7
I need to name the workbook tab as my cell C4 (which is simply a date).

I am not very familiar with VBA's and macros so I am sure that the codes I am copy/pasting are correct, I just need a more detailed breakdown of what to edit once I have pasted the code. Please help. This is the code I used and the cell is C4. What should I edit?

Sub TabFromCell()
Sheets(1).Name = Sheets(1).Range("A1")
End Sub


Thank you so much.


See More: How can I link a cell to a tab name?

Report •


✔ Best Answer
May 16, 2011 at 09:54:04
How about this:

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error GoTo NoDate
   If Target.Address = "$C$4" Then
    ActiveSheet.Name = Month(Target) & "-" & _
                       Day(Target) & "-" & _
                       Year(Target)
   End If
NoDate:
End Sub

This should work for dates and not throw up an error if the value isn't a date.

P.S. before posting any more code, please click on my signature line for instructions on how to post code in this forum.

Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
May 16, 2011 at 07:38:38
I mean this in the nicest way and am just trying to teach you without doing it for you.

If you look at the code you posted, do you see anything that looks like a cell reference? The cell you want to use is C4, so do you see anything in the code that looks similar to C4 or B3 or F6, just as examples?

That's what you need to change so that the code picks up the value in C4.

However, I would like add one more comment. You used the words "which is simply a date".

Dates in Excel are far from "simple" since that are stored internally as a number with the integer portion representing the date and the decimal portion representing the time.

What you see displayed in the cell is based solely on how the cell is formatted.

What you get as a tab name after running the code above may or may not be what you expect. In fact, it may not even work at all.

You may need to supply some more details before we can offer any more assitance. Specifically, what do you have in C4 and what do you want the Tab name to show.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 16, 2011 at 07:53:56
This is how I entered the code.

Sub TabFromCell()
Sheets(1).Name = Sheets(1).Range("C4")
End Sub

I thought of the formatting as well. I tried to change it to text and I also tried formatting as date 20-May-11, since the tab name will not accept (/). But neither worked.

I need the tab name to reflect a week ending date from C4. This workbook will be used for the rest of the year, so there will be many tabs. Any format on the tab will work
EX) 052011.... 05-20-11.... May 20.... May, 20 11. Just as long as there is some indication of a date.

Thank you.


Report •

#3
May 16, 2011 at 08:15:04
When you say that you need "a week ending date from C4", is the "week ending date" already in C4 or does the code need to calculate the "week ending date" based on the date in C4?

Do you want this tab name updated as soon as the date is entered in C4?

You can use a Worksheet_Change macro which will fire whenever you change C4, instead of having to manually run the code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 16, 2011 at 08:42:20
The week ending date will be manually entered, there is no formula to compute.

Yes, I want the tab updated once the date is entered into cell C4.

I also tried the below code and couldn't get it to work either.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$4" Then
ActiveSheet.Name = Target.Text
End If
End Sub


Report •

#5
May 16, 2011 at 09:54:04
✔ Best Answer
How about this:

Private Sub Worksheet_Change(ByVal Target As Range)
 On Error GoTo NoDate
   If Target.Address = "$C$4" Then
    ActiveSheet.Name = Month(Target) & "-" & _
                       Day(Target) & "-" & _
                       Year(Target)
   End If
NoDate:
End Sub

This should work for dates and not throw up an error if the value isn't a date.

P.S. before posting any more code, please click on my signature line for instructions on how to post code in this forum.

Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
May 16, 2011 at 10:54:07
Here goes. I will let you know if it works. Thank you so much!

Report •

#7
May 16, 2011 at 11:07:50
Wow, it worked. Thank you so much! Have a fantastic day!

Report •


Ask Question