Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get a new calculated date from different date ranges? | Excel Worksheet Functions | |||
pivot with calculated date | Charts and Charting in Excel | |||
How can I match a calculated date to closest date from a list? | Excel Worksheet Functions | |||
Totals calculated by date | Excel Discussion (Misc queries) | |||
how to change a calculated cell to = the calculated value | Excel Discussion (Misc queries) |