Can I have the TAB name be a calculated field...?

[Kelvin Beaton]

I have a spreadsheet that has a calculated date field in "=H34+3"

I would love for the TAB to equal that value.

I think I've tried to figure this out a long time back, but I think I got
stuck becasue it is a calculated fileld.

Anyone know if this can be done, and how?

Thanks

Kelvin

 

[Kelvin Beaton]

Oh, and I like the date in the TAB to be formatted, like 6/4/07.

Thanks

Kelvin

 

[JE McGimpsey]

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose View Code):


Private Sub Worksheet_Calculate()
Const sCELLADDRESS As String = "H35" 'Change to suit
On Error Resume Next
Me.Name = Range(sCELLADDRESS).Text
On Error GoTo 0
End Sub

 

[JE McGimpsey]

Using my suggested solution, instead of

Me.Name = Range(sCELLADDRESS).Text

you could use

Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy")

or some other format. Note that you can't use slashes in tab names.

 

[Guest]

I think you will find TABs cannot be formatted as you require: try renaming
one.

 

[Guest]

Tabs can not be formated with any of these characters (\/:*?<>|) it .
So 6/1/07 will not work
You can use VBA to change the tab name to = 6107 0r 060107 or Mar 06,07 and
so on

 

[Keith74]

Well i think the code would be soming like

Sheets("Sheet1").Name = activesheet.cells(rowno, colno).value &
Format(somedate, "d/m/yy")

Stick under the desired event and simmer gently for 30 mins

hth

 

[Kelvin Beaton]

Ok, non of the sugestions seem to work...

I have a calacualted field in D5 on a data... what is supposed to fire this
off?
I put this code in Sheet3 and the sheet TAB still says Sheet3
When I open the spreadsheet it asked me if I want to enable Macros, and I
say yes...

I seem to be missing something in what I'm doing...
I right mouse clicked on the tab and choose View Code and pasted it there. I
pretty sure that's what I was supposed to do...

Private Sub Worksheet_Calculate()
Const sCELLADDRESS As String = "D5" 'Change to suit
On Error Resume Next
Me.Name = Range(sCELLADDRESS).Text
On Error GoTo 0
End Sub

Can you think of anything I'm doing wrong?

Thanks

Kelvin

PS sorry, I do want the format of the date to be 2-2-07...

 

[Guest]

Works fine for me: trying entering data in any cell.

 

[Kelvin Beaton]

Thanks
Here's what I'm using. I have this pasted in the Code for the tab and if I
run it manually it works, what do I need to do to make this code fire?

What I'm doing is, I use this Excel file as a timesheet. the value in cell
D5 is the date the timesheet is to be handed in...
So for each pay period I have a Tab and I want the TAB to be the sheet\D5
value to show.
I really only need this to update once a year... as the first day of the pay
period changed each year...

I guess my question is, how do I fire this off for each page?
If I have this code in there, will I then always be prompted to "Enable
Marcos"?

Thanks

Private Sub Worksheet_Calculate()
Const sCELLADDRESS As String = "D5" 'Change to suit
On Error Resume Next
Me.Name = Format(Range(sCELLADDRESS).Value, "m-d-yy")
On Error GoTo 0
End Sub

 

[Kelvin Beaton]

I'm not typing the date into the field.

I enter one date on the first spreadsheet and the rest of the sheets
calculate off that. so an after update event wouldn't like fire if I don't
make the change on that sheet.
Also, I don't if this matters, but the sheets are locked for the most part.
the cell with the date in it is one that is locked, since it's calcualted
there's no reason for the user to type there...

Kelvin

 

[Guest]

White out over sheet1 tab let dry then with sharpie put 6/1/07
should work
for more sheets repeat steps

 

[Kelvin Beaton]

Ok, your humor is interesting, but I don't see how it is helpful at this
point ..........

 

[Guest]

to change all of the sheets to the date in D5 try a macro like

Sub dt()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
sh.Activate
shn = Cells(5, 4)
sh.Name = Format(shn, "m-d-yy")
Next sh

End Sub

 

[JE McGimpsey]

Can you think of anything I'm doing wrong?

Do something to make the sheet recalculate, and make sure D5 contains
text that is a valid sheet non-duplicate sheet name.