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

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

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

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

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

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 ,
"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?

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

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

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

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

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

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" wrote in message
...
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 ,
"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?

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

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" wrote in message
...
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" wrote in message
...
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 ,
"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?

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

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
...
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" wrote in message
...
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" wrote in message
...
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 ,
"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?

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

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

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

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 ,
"Bob Phillips" 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" wrote in message
...
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 ,
"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?

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

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