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

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Can I have the TAB name be a calculated field...? (https://www.excelbanter.com/excel-discussion-misc-queries/145060-can-i-have-tab-name-calculated-field.html)

Kelvin Beaton

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

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

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

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

Thanks

Kelvin

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

JE McGimpsey

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

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

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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

JE McGimpsey

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

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.

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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

Thanks

Kelvin

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

Toppers

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

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

"Kelvin Beaton" wrote:

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

Thanks

Kelvin

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

Mike

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

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

"Kelvin Beaton" wrote:

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

Thanks

Kelvin

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

Keith74

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

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

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

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...

"JE McGimpsey" wrote in message
...
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

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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

Toppers

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

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

"Kelvin Beaton" wrote:

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...

"JE McGimpsey" wrote in message
...
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

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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

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

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

"JE McGimpsey" wrote in message
...
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.

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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

Thanks

Kelvin

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

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

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

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

"JE McGimpsey" wrote in message
...
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.

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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

Thanks

Kelvin

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

Mike

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

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

"Kelvin Beaton" wrote:

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

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

"JE McGimpsey" wrote in message
...
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.

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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

Thanks

Kelvin

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

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

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

"Mike" wrote in message
...
White out over sheet1 tab let dry then with sharpie put 6/1/07
should work
for more sheets repeat steps

"Kelvin Beaton" wrote:

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

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

"JE McGimpsey" wrote in message
...
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.

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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

Thanks

Kelvin

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

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

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

"Kelvin Beaton" wrote:

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

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

"JE McGimpsey" wrote in message
...
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.

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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

Thanks

Kelvin

"Kelvin Beaton" <kelvin at mccsa dot com wrote in message
...
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

JE McGimpsey

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

In article ,
"Kelvin Beaton" <kelvin at mccsa dot com wrote:

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.

mikerickson

Put this in the ThisWorkbook CodeModule. It will automaticaly update the tab of every sheet in the workbook. Yes, you will have to enable macros.

Code:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    Application.EnableEvents = False

        Sh.Name = Format(Range("D5").Value, "m-d-yy")

    Application.EnableEvents = True

End Sub

Quote:

Originally Posted by Kelvin Beaton (Post 504162)

All times are GMT +1. The time now is 08:46 AM.