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