I want the calculated date value of a cell to be the TAB value...

[Kelvin Beaton]

In "D22" I have a formula "=H9+3". I want the calculated value of "D22" to
display as a date in the worksheet TAB.

This code looks like it should work with a date, but doesn't
++++
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim wksh As Worksheet
Dim sStr As String
If Target.Address = "D22" Then
If IsDate(Target) Then
sStr = Format(Target.Value, "dd-mmm-yyyy")
' does the sheet already have that name
If Sh.Name = sStr Then Exit Sub
' Does another sheet have that name
On Error Resume Next
Set wksh = Nothing
Set wksh = Worksheets(sStr)
On Error GoTo 0
If Not wksh Is Nothing Then
MsgBox "There is already a sheet with the name " & sStr
Exit Sub
End If
' Everything OK, rename sheet
Sh.Name = sStr
End If
End If
End Sub
++++

Anyone out there have code that works with dates?

Any help would be appreciated!!

Kelvin

[TomHinkle]

well I see one problem

you defined Target as a Range object.

then run the function isdate on it..

Target will NEVER be a date...

Target.value might work better.

HTH


"Kelvin Beaton" wrote:

> In "D22" I have a formula "=H9+3". I want the calculated value of "D22" to
> display as a date in the worksheet TAB.
>
> This code looks like it should work with a date, but doesn't
> ++++
> Private Sub Workbook_SheetChange(ByVal Sh As Object, _
> ByVal Target As Excel.Range)
> Dim wksh As Worksheet
> Dim sStr As String
> If Target.Address = "D22" Then
> If IsDate(Target) Then
> sStr = Format(Target.Value, "dd-mmm-yyyy")
> ' does the sheet already have that name
> If Sh.Name = sStr Then Exit Sub
> ' Does another sheet have that name
> On Error Resume Next
> Set wksh = Nothing
> Set wksh = Worksheets(sStr)
> On Error GoTo 0
> If Not wksh Is Nothing Then
> MsgBox "There is already a sheet with the name " & sStr
> Exit Sub
> End If
> ' Everything OK, rename sheet
> Sh.Name = sStr
> End If
> End If
> End Sub
> ++++
>
> Anyone out there have code that works with dates?
>
> Any help would be appreciated!!
>
> Kelvin
>
>
>

[JE McGimpsey]

That's not the problem - The .Value property is the default property
for the Range object, so

IsDate(Range)

is equivalent to

IsDate(Range.Value)

In article <6254BC30-3751-400B-B2A7-4EB24180DEB9@microsoft.com>,
TomHinkle <TomHinkle@discussions.microsoft.com> wrote:

> well I see one problem
>
> you defined Target as a Range object.
>
> then run the function isdate on it..
>
> Target will NEVER be a date...
>
> Target.value might work better.

[Bob Phillips]

Not so, Target is a range and has Value as its default property, so it will
test that.

the problem IMO is that you test an absolute address for a relative value.
Try

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
Dim wksh As Worksheet
Dim sStr As String
If Target.Address(False, False) = "D22" Then
If IsDate(Target) Then
sStr = Format(Target.Value, "dd-mmm-yyyy")
' does the sheet already have that name
If Sh.Name = sStr Then Exit Sub
' Does another sheet have that name
On Error Resume Next
Set wksh = Nothing
Set wksh = Worksheets(sStr)
On Error GoTo 0
If Not wksh Is Nothing Then
MsgBox "There is already a sheet with the name " & sStr
Exit Sub
End If
' Everything OK, rename sheet
Sh.Name = sStr
End If
End If
End Sub



--
HTH

Bob Phillips

"TomHinkle" <TomHinkle@discussions.microsoft.com> wrote in message
news:6254BC30-3751-400B-B2A7-4EB24180DEB9@microsoft.com...
> well I see one problem
>
> you defined Target as a Range object.
>
> then run the function isdate on it..
>
> Target will NEVER be a date...
>
> Target.value might work better.
>
> HTH
>
>
> "Kelvin Beaton" wrote:
>
> > In "D22" I have a formula "=H9+3". I want the calculated value of "D22"
to
> > display as a date in the worksheet TAB.
> >
> > This code looks like it should work with a date, but doesn't
> > ++++
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, _
> > ByVal Target As Excel.Range)
> > Dim wksh As Worksheet
> > Dim sStr As String
> > If Target.Address = "D22" Then
> > If IsDate(Target) Then
> > sStr = Format(Target.Value, "dd-mmm-yyyy")
> > ' does the sheet already have that name
> > If Sh.Name = sStr Then Exit Sub
> > ' Does another sheet have that name
> > On Error Resume Next
> > Set wksh = Nothing
> > Set wksh = Worksheets(sStr)
> > On Error GoTo 0
> > If Not wksh Is Nothing Then
> > MsgBox "There is already a sheet with the name " & sStr
> > Exit Sub
> > End If
> > ' Everything OK, rename sheet
> > Sh.Name = sStr
> > End If
> > End If
> > End Sub
> > ++++
> >
> > Anyone out there have code that works with dates?
> >
> > Any help would be appreciated!!
> >
> > Kelvin
> >
> >
> >

[JE McGimpsey]

It's not the dates, it's your choice of event. Worbook_Change doesn't
fire when a calculation changes a cell value. You could change the
Target address to H9, if that has a user-entered value, for instance:

If Target.Address(False, False) = "H9" Then
With Range("D22")
If IsDate(.Value) Then
sStr = Format(.Value, "dd-mmm-yyyy")

and so on.

Note that your Target.Address = "D22" would never be true - the Address
property returns an absolute reference ("$D$22") by default).

Or you could use the Workbook_SheetCalculate() Event and check D22's
value directly.




In article <eS$cZrPcFHA.3560@TK2MSFTNGP10.phx.gbl>,
"Kelvin Beaton" <kelvin at mccsa dot com> wrote:

> In "D22" I have a formula "=H9+3". I want the calculated value of "D22" to
> display as a date in the worksheet TAB.
>
> This code looks like it should work with a date, but doesn't
> ++++
> Private Sub Workbook_SheetChange(ByVal Sh As Object, _
> ByVal Target As Excel.Range)
> Dim wksh As Worksheet
> Dim sStr As String
> If Target.Address = "D22" Then
> If IsDate(Target) Then
> sStr = Format(Target.Value, "dd-mmm-yyyy")
> ' does the sheet already have that name
> If Sh.Name = sStr Then Exit Sub
> ' Does another sheet have that name
> On Error Resume Next
> Set wksh = Nothing
> Set wksh = Worksheets(sStr)
> On Error GoTo 0
> If Not wksh Is Nothing Then
> MsgBox "There is already a sheet with the name " & sStr
> Exit Sub
> End If
> ' Everything OK, rename sheet
> Sh.Name = sStr
> End If
> End If
> End Sub
> ++++
>
> Anyone out there have code that works with dates?

[Bob Phillips]

JE,

If he uses SheetChange on D22, that will work when he creates the formula,
it will remain the same each time H9 changes, as long as he corrects the
address problem that you also picked up.

Bob

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-036A6A.10502614062005@msnews.microsoft.com...
> It's not the dates, it's your choice of event. Worbook_Change doesn't
> fire when a calculation changes a cell value. You could change the
> Target address to H9, if that has a user-entered value, for instance:
>
> If Target.Address(False, False) = "H9" Then
> With Range("D22")
> If IsDate(.Value) Then
> sStr = Format(.Value, "dd-mmm-yyyy")
>
> and so on.
>
> Note that your Target.Address = "D22" would never be true - the Address
> property returns an absolute reference ("$D$22") by default).
>
> Or you could use the Workbook_SheetCalculate() Event and check D22's
> value directly.
>
>
>
>
> In article <eS$cZrPcFHA.3560@TK2MSFTNGP10.phx.gbl>,
> "Kelvin Beaton" <kelvin at mccsa dot com> wrote:
>
> > In "D22" I have a formula "=H9+3". I want the calculated value of "D22"
to
> > display as a date in the worksheet TAB.
> >
> > This code looks like it should work with a date, but doesn't
> > ++++
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, _
> > ByVal Target As Excel.Range)
> > Dim wksh As Worksheet
> > Dim sStr As String
> > If Target.Address = "D22" Then
> > If IsDate(Target) Then
> > sStr = Format(Target.Value, "dd-mmm-yyyy")
> > ' does the sheet already have that name
> > If Sh.Name = sStr Then Exit Sub
> > ' Does another sheet have that name
> > On Error Resume Next
> > Set wksh = Nothing
> > Set wksh = Worksheets(sStr)
> > On Error GoTo 0
> > If Not wksh Is Nothing Then
> > MsgBox "There is already a sheet with the name " & sStr
> > Exit Sub
> > End If
> > ' Everything OK, rename sheet
> > Sh.Name = sStr
> > End If
> > End If
> > End Sub
> > ++++
> >
> > Anyone out there have code that works with dates?

[Kelvin Beaton]

Ok, I should have added one more detail... I'm not a programmer.
These sound like great ideas, but I don't have the skill to rewrite this
code.

I should have said I found the code and would someone have code that works?

You help would be apprediated...

Kelvin



"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:u2gyZQQcFHA.2736@TK2MSFTNGP12.phx.gbl...
> JE,
>
> If he uses SheetChange on D22, that will work when he creates the formula,
> it will remain the same each time H9 changes, as long as he corrects the
> address problem that you also picked up.
>
> Bob
>
> "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
> news:jemcgimpsey-036A6A.10502614062005@msnews.microsoft.com...
>> It's not the dates, it's your choice of event. Worbook_Change doesn't
>> fire when a calculation changes a cell value. You could change the
>> Target address to H9, if that has a user-entered value, for instance:
>>
>> If Target.Address(False, False) = "H9" Then
>> With Range("D22")
>> If IsDate(.Value) Then
>> sStr = Format(.Value, "dd-mmm-yyyy")
>>
>> and so on.
>>
>> Note that your Target.Address = "D22" would never be true - the Address
>> property returns an absolute reference ("$D$22") by default).
>>
>> Or you could use the Workbook_SheetCalculate() Event and check D22's
>> value directly.
>>
>>
>>
>>
>> In article <eS$cZrPcFHA.3560@TK2MSFTNGP10.phx.gbl>,
>> "Kelvin Beaton" <kelvin at mccsa dot com> wrote:
>>
>> > In "D22" I have a formula "=H9+3". I want the calculated value of "D22"
> to
>> > display as a date in the worksheet TAB.
>> >
>> > This code looks like it should work with a date, but doesn't
>> > ++++
>> > Private Sub Workbook_SheetChange(ByVal Sh As Object, _
>> > ByVal Target As Excel.Range)
>> > Dim wksh As Worksheet
>> > Dim sStr As String
>> > If Target.Address = "$D$22" Then
>> > If IsDate(Target) Then
>> > sStr = Format(Target.Value, "dd-mmm-yyyy")
>> > ' does the sheet already have that name
>> > If Sh.Name = sStr Then Exit Sub
>> > ' Does another sheet have that name
>> > On Error Resume Next
>> > Set wksh = Nothing
>> > Set wksh = Worksheets(sStr)
>> > On Error GoTo 0
>> > If Not wksh Is Nothing Then
>> > MsgBox "There is already a sheet with the name " & sStr
>> > Exit Sub
>> > End If
>> > ' Everything OK, rename sheet
>> > Sh.Name = sStr
>> > End If
>> > End If
>> > End Sub
>> > ++++
>> >
>> > Anyone out there have code that works with dates?
>
>

[Bob Phillips]

You don't need any code. Just format D22 as you want it and all will be
fine.

--
HTH

Bob Phillips

"Kelvin Beaton" <kelvin at mccsa dot com> wrote in message
news:%23$FPChQcFHA.2520@TK2MSFTNGP09.phx.gbl...
> Ok, I should have added one more detail... I'm not a programmer.
> These sound like great ideas, but I don't have the skill to rewrite this
> code.
>
> I should have said I found the code and would someone have code that
works?
>
> You help would be apprediated...
>
> Kelvin
>
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:u2gyZQQcFHA.2736@TK2MSFTNGP12.phx.gbl...
> > JE,
> >
> > If he uses SheetChange on D22, that will work when he creates the
formula,
> > it will remain the same each time H9 changes, as long as he corrects the
> > address problem that you also picked up.
> >
> > Bob
> >
> > "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
> > news:jemcgimpsey-036A6A.10502614062005@msnews.microsoft.com...
> >> It's not the dates, it's your choice of event. Worbook_Change doesn't
> >> fire when a calculation changes a cell value. You could change the
> >> Target address to H9, if that has a user-entered value, for instance:
> >>
> >> If Target.Address(False, False) = "H9" Then
> >> With Range("D22")
> >> If IsDate(.Value) Then
> >> sStr = Format(.Value, "dd-mmm-yyyy")
> >>
> >> and so on.
> >>
> >> Note that your Target.Address = "D22" would never be true - the Address
> >> property returns an absolute reference ("$D$22") by default).
> >>
> >> Or you could use the Workbook_SheetCalculate() Event and check D22's
> >> value directly.
> >>
> >>
> >>
> >>
> >> In article <eS$cZrPcFHA.3560@TK2MSFTNGP10.phx.gbl>,
> >> "Kelvin Beaton" <kelvin at mccsa dot com> wrote:
> >>
> >> > In "D22" I have a formula "=H9+3". I want the calculated value of
"D22"
> > to
> >> > display as a date in the worksheet TAB.
> >> >
> >> > This code looks like it should work with a date, but doesn't
> >> > ++++
> >> > Private Sub Workbook_SheetChange(ByVal Sh As Object, _
> >> > ByVal Target As Excel.Range)
> >> > Dim wksh As Worksheet
> >> > Dim sStr As String
> >> > If Target.Address = "$D$22" Then
> >> > If IsDate(Target) Then
> >> > sStr = Format(Target.Value, "dd-mmm-yyyy")
> >> > ' does the sheet already have that name
> >> > If Sh.Name = sStr Then Exit Sub
> >> > ' Does another sheet have that name
> >> > On Error Resume Next
> >> > Set wksh = Nothing
> >> > Set wksh = Worksheets(sStr)
> >> > On Error GoTo 0
> >> > If Not wksh Is Nothing Then
> >> > MsgBox "There is already a sheet with the name " & sStr
> >> > Exit Sub
> >> > End If
> >> > ' Everything OK, rename sheet
> >> > Sh.Name = sStr
> >> > End If
> >> > End If
> >> > End Sub
> >> > ++++
> >> >
> >> > Anyone out there have code that works with dates?
> >
> >
>
>

[JE McGimpsey]

Hmmm..

After putting the code in the ThisWorkbook module

H9: 6/14/2005
D22: =H9+3

When I change the date in H9, the value in D22 changes, but not the Tab
name, as you note. But the OP's requirement was "I want the calculated
value of "D22" to display as a date in the worksheet TAB." Doesn't that
mean that the worksheet name should reflect the cell value as it changes?



In article <u2gyZQQcFHA.2736@TK2MSFTNGP12.phx.gbl>,
"Bob Phillips" <phillips@tiscali.co.uk> wrote:

> JE,
>
> If he uses SheetChange on D22, that will work when he creates the formula,
> it will remain the same each time H9 changes, as long as he corrects the
> address problem that you also picked up.
>
> Bob
>
> "JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
> news:jemcgimpsey-036A6A.10502614062005@msnews.microsoft.com...
> > It's not the dates, it's your choice of event. Worbook_Change doesn't
> > fire when a calculation changes a cell value. You could change the
> > Target address to H9, if that has a user-entered value, for instance:
> >
> > If Target.Address(False, False) = "H9" Then
> > With Range("D22")
> > If IsDate(.Value) Then
> > sStr = Format(.Value, "dd-mmm-yyyy")
> >
> > and so on.
> >
> > Note that your Target.Address = "D22" would never be true - the Address
> > property returns an absolute reference ("$D$22") by default).
> >
> > Or you could use the Workbook_SheetCalculate() Event and check D22's
> > value directly.
> >
> >
> >
> >
> > In article <eS$cZrPcFHA.3560@TK2MSFTNGP10.phx.gbl>,
> > "Kelvin Beaton" <kelvin at mccsa dot com> wrote:
> >
> > > In "D22" I have a formula "=H9+3". I want the calculated value of "D22"
> to
> > > display as a date in the worksheet TAB.
> > >
> > > This code looks like it should work with a date, but doesn't
> > > ++++
> > > Private Sub Workbook_SheetChange(ByVal Sh As Object, _
> > > ByVal Target As Excel.Range)
> > > Dim wksh As Worksheet
> > > Dim sStr As String
> > > If Target.Address = "D22" Then
> > > If IsDate(Target) Then
> > > sStr = Format(Target.Value, "dd-mmm-yyyy")
> > > ' does the sheet already have that name
> > > If Sh.Name = sStr Then Exit Sub
> > > ' Does another sheet have that name
> > > On Error Resume Next
> > > Set wksh = Nothing
> > > Set wksh = Worksheets(sStr)
> > > On Error GoTo 0
> > > If Not wksh Is Nothing Then
> > > MsgBox "There is already a sheet with the name " & sStr
> > > Exit Sub
> > > End If
> > > ' Everything OK, rename sheet
> > > Sh.Name = sStr
> > > End If
> > > End If
> > > End Sub
> > > ++++
> > >
> > > Anyone out there have code that works with dates?

[Bob Phillips]

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-4E5360.16530014062005@msnews.microsoft.com...
> Hmmm..

LOL. You often do that as a precursor to arguing against something.

> After putting the code in the ThisWorkbook module
>
> H9: 6/14/2005
> D22: =H9+3
>
> When I change the date in H9, the value in D22 changes, but not the Tab
> name, as you note. But the OP's requirement was "I want the calculated
> value of "D22" to display as a date in the worksheet TAB." Doesn't that
> mean that the worksheet name should reflect the cell value as it changes?

You are right, I had lost track of that original part about the TAB.

[TomHinkle]

Put this code in the worksheet change event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$D$22" Then
Target.Parent.Name = "MyNewTab " & Format(Target.Value, "mmm, dd
yyyy")
End If
End Sub




"Kelvin Beaton" wrote:

> In "D22" I have a formula "=H9+3". I want the calculated value of "D22" to
> display as a date in the worksheet TAB.
>
> This code looks like it should work with a date, but doesn't
> ++++
> Private Sub Workbook_SheetChange(ByVal Sh As Object, _
> ByVal Target As Excel.Range)
> Dim wksh As Worksheet
> Dim sStr As String
> If Target.Address = "D22" Then
> If IsDate(Target) Then
> sStr = Format(Target.Value, "dd-mmm-yyyy")
> ' does the sheet already have that name
> If Sh.Name = sStr Then Exit Sub
> ' Does another sheet have that name
> On Error Resume Next
> Set wksh = Nothing
> Set wksh = Worksheets(sStr)
> On Error GoTo 0
> If Not wksh Is Nothing Then
> MsgBox "There is already a sheet with the name " & sStr
> Exit Sub
> End If
> ' Everything OK, rename sheet
> Sh.Name = sStr
> End If
> End If
> End Sub
> ++++
>
> Anyone out there have code that works with dates?
>
> Any help would be appreciated!!
>
> Kelvin
>
>
>