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

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
news:(E-Mail Removed)...
>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
>

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 <(E-Mail Removed)>,
"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

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 <(E-Mail Removed)>,
"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
> news:(E-Mail Removed)...
> >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
> >

I think you will find TABs cannot be formatted as you require: 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
> news:(E-Mail Removed)...
> >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
> >
>
>
>

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
> news:(E-Mail Removed)...
> >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
> >
>
>
>

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

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" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> 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 <(E-Mail Removed)>,
> "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

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" <(E-Mail Removed)> wrote in message
> news:jemcgimpsey-(E-Mail Removed)...
> > 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 <(E-Mail Removed)>,
> > "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
>
>
>

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" <(E-Mail Removed)> wrote in message
news:jemcgimpsey-(E-Mail Removed)...
> 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 <(E-Mail Removed)>,
> "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
>> news:(E-Mail Removed)...
>> >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
>> >