Recurring problem - user input date from messagebox doesn't get formatted correctly in secondary cells.

I recvd the fabulous code below some time back for prompting users for
dates which then gets dumped into the sheet. I've modified it several
times and use it throughout several workbooks now and it usu. works
perfectly.

However, in cases where cells are dependent on 2 different versions of
the date differentiated by cell formatting re the date display, the
format in secondary cell doesn't display properlly as per that cell's
formatting. In the case today, it displays exactly the same as in the
first cell.

----------------------------------------------------------------------
Sub NewDate()
'
ActiveSheet.Unprotect 'place at the beginning of the code
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter a start date." & vbCrLf & vbCrLf & _
"(By the way, Excel is pretty forgiving of the date
style you use when you enter that date.)", _
Title:="Thyroid Log", _
Default:=Format(Date, "yyyy/mm/dd"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
Range("B2").Value = Format(CDate(vResponse), "yyyy/mm/dd")
ActiveSheet.Protect 'place at the end of the code
End Sub
----------------------------------------------------------------------

So, B2 user input date gets dumped as yyyy.mm.dd.ddd in B2 which is
how the cell is formatted. Perfect.

B1 is supposed to take the value of B2 but formatted as ddd; however,
I'm getting the same date display as in B2 - yyyy.mm.dd.ddd instead of
ddd. If I re-enter the exact date manually that was just dumped into
the cell by the above code, _then_ the cell displays the ddd format
correctly (??).

Can this be resolved? It's only the one cell that is not working.
All the rest of the sheet seems fine.

Thank you! :oD

The line of code...

Range("B2").Value = Format(CDate(vResponse), "yyyy/mm/dd")

Places the Text of your date in the cell, not the Excel date. You can not do
what you want with text you need an actual date.

Try something more like (untested but it should be close)

with Range("B2")
.numberformat = "yyyy/mm/dd"
.value = CDate(vResponse)
end with
--
HTH...

Jim Thomlinson


"StargateFan" wrote:

> I recvd the fabulous code below some time back for prompting users for
> dates which then gets dumped into the sheet. I've modified it several
> times and use it throughout several workbooks now and it usu. works
> perfectly.
>
> However, in cases where cells are dependent on 2 different versions of
> the date differentiated by cell formatting re the date display, the
> format in secondary cell doesn't display properlly as per that cell's
> formatting. In the case today, it displays exactly the same as in the
> first cell.
>
> ----------------------------------------------------------------------
> Sub NewDate()
> '
> ActiveSheet.Unprotect 'place at the beginning of the code
> On Error Resume Next
> ActiveSheet.ShowAllData
> On Error GoTo 0
>
> Dim vResponse As Variant
> Do
> vResponse = Application.InputBox( _
> Prompt:="Enter a start date." & vbCrLf & vbCrLf & _
> "(By the way, Excel is pretty forgiving of the date
> style you use when you enter that date.)", _
> Title:="Thyroid Log", _
> Default:=Format(Date, "yyyy/mm/dd"), _
> Type:=2)
> If vResponse = False Then Exit Sub 'User cancelled
> Loop Until IsDate(vResponse)
> Range("B2").Value = Format(CDate(vResponse), "yyyy/mm/dd")
> ActiveSheet.Protect 'place at the end of the code
> End Sub
> ----------------------------------------------------------------------
>
> So, B2 user input date gets dumped as yyyy.mm.dd.ddd in B2 which is
> how the cell is formatted. Perfect.
>
> B1 is supposed to take the value of B2 but formatted as ddd; however,
> I'm getting the same date display as in B2 - yyyy.mm.dd.ddd instead of
> ddd. If I re-enter the exact date manually that was just dumped into
> the cell by the above code, _then_ the cell displays the ddd format
> correctly (??).
>
> Can this be resolved? It's only the one cell that is not working.
> All the rest of the sheet seems fine.
>
> Thank you! :oD
>
>

On Fri, 3 Feb 2006 09:11:12 -0800, "Jim Thomlinson"
<[email protected]> wrote:

>The line of code...
>
>Range("B2").Value = Format(CDate(vResponse), "yyyy/mm/dd")
>
>Places the Text of your date in the cell, not the Excel date. You can not do
>what you want with text you need an actual date.
>
>Try something more like (untested but it should be close)
>
>with Range("B2")
> .numberformat = "yyyy/mm/dd"
> .value = CDate(vResponse)
>end with

Sorry for the long delay. I finished a job contract and then went on
a trip outside the country to visit family.

I finally had a chance to work on this but I'm afraid that it's still
not working properly. To complicate matters, I now have XL2003, and
don't know if that's affecting anything.

What I'm trying to do is to have date dumped as if I were just
pressing ^+; so that the cell's formatting is honoured. I'm not
getting that. Now I don't know if I modified the formula correctly as
per your instructions, so will start there.

The macro now looks like this:
----------------------------------------------------------------------
Sub NewDate()
'
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter a start date." & vbCrLf & vbCrLf & _
"(By the way, Excel is pretty forgiving of the date
style you use when you enter that date.)", _
Title:="Iron Juice Treatment Log", _
Default:=Format(Date, "yyyy/mm/dd"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
With Range("B2")
.NumberFormat = "yyyy/mm/dd"
.Value = CDate(vResponse)
End With

End Sub
----------------------------------------------------------------------

Any ideas on how to get XL2003 to obey a simple date dump similar to
^+; but through the user input box?

Thank you.

>--
>HTH...
>
>Jim Thomlinson
>
>
>"StargateFan" wrote:
>
>> I recvd the fabulous code below some time back for prompting users for
>> dates which then gets dumped into the sheet. I've modified it several
>> times and use it throughout several workbooks now and it usu. works
>> perfectly.
>>
>> However, in cases where cells are dependent on 2 different versions of
>> the date differentiated by cell formatting re the date display, the
>> format in secondary cell doesn't display properlly as per that cell's
>> formatting. In the case today, it displays exactly the same as in the
>> first cell.
>>
>> ----------------------------------------------------------------------
>> Sub NewDate()
>> '
>> ActiveSheet.Unprotect 'place at the beginning of the code
>> On Error Resume Next
>> ActiveSheet.ShowAllData
>> On Error GoTo 0
>>
>> Dim vResponse As Variant
>> Do
>> vResponse = Application.InputBox( _
>> Prompt:="Enter a start date." & vbCrLf & vbCrLf & _
>> "(By the way, Excel is pretty forgiving of the date
>> style you use when you enter that date.)", _
>> Title:="Thyroid Log", _
>> Default:=Format(Date, "yyyy/mm/dd"), _
>> Type:=2)
>> If vResponse = False Then Exit Sub 'User cancelled
>> Loop Until IsDate(vResponse)
>> Range("B2").Value = Format(CDate(vResponse), "yyyy/mm/dd")
>> ActiveSheet.Protect 'place at the end of the code
>> End Sub
>> ----------------------------------------------------------------------
>>
>> So, B2 user input date gets dumped as yyyy.mm.dd.ddd in B2 which is
>> how the cell is formatted. Perfect.
>>
>> B1 is supposed to take the value of B2 but formatted as ddd; however,
>> I'm getting the same date display as in B2 - yyyy.mm.dd.ddd instead of
>> ddd. If I re-enter the exact date manually that was just dumped into
>> the cell by the above code, _then_ the cell displays the ddd format
>> correctly (??).
>>
>> Can this be resolved? It's only the one cell that is not working.
>> All the rest of the sheet seems fine.
>>
>> Thank you! :oD
>>
>>

On Sat, 04 Mar 2006 23:19:24 -0500, StargateFan
<IDon'tAcceptSpam@IDon'tAcceptSpam.com> wrote:

>On Fri, 3 Feb 2006 09:11:12 -0800, "Jim Thomlinson"
><[email protected]> wrote:
>
>>The line of code...
>>
>>Range("B2").Value = Format(CDate(vResponse), "yyyy/mm/dd")
>>
>>Places the Text of your date in the cell, not the Excel date. You can not do
>>what you want with text you need an actual date.

[snip]

Just wanted to give closure to this. Finally got this to work. The
problem now lay in the line:
..numberformat = "yyyy/mm/dd"
I changed format to mmm.dd.yyyy and it now works fine.

>What I'm trying to do is to have date dumped as if I were just
>pressing ^+; so that the cell's formatting is honoured. I'm not
>getting that. Now I don't know if I modified the formula correctly as
>per your instructions, so will start there.

[snip]

This is what the working macro looks like now:
----------------------------------------------------------------------
Sub RequestDateFromUser()
'
ActiveSheet.Unprotect 'place at the beginning of the code
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Dim vResponse As Variant
Do
vResponse = Application.InputBox( _
Prompt:="Enter a start date." & vbCrLf & vbCrLf & _
"(By the way, Excel is pretty forgiving of the date
style you use when you enter that date.)", _
Title:="Health Log", _
Default:=Format(Date, "yyyy/mm/dd"), _
Type:=2)
If vResponse = False Then Exit Sub 'User cancelled
Loop Until IsDate(vResponse)
With Range("B2")
.NumberFormat = "mmm.dd.yyyy"
.Value = CDate(vResponse)
End With

ActiveSheet.Protect 'place at the end of the code
End Sub
----------------------------------------------------------------------

Thank you.