S
StargateFan
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! D
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! D