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

 

[Guest]

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

 

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

 

[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)

 

[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

 

[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

 

[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]

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

 

[Guest]

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

 

[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?

 

[Bob Phillips]

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.