ALLAROUNDOFFICE
Board Regular
- Joined
- Apr 26, 2014
- Messages
- 61
How can I hard code a column of cells to convert the user entered data of "042614" or "42614" into a specific date format such as 04/26/2014?
Could it be possible that my spreadsheet isn't even recognizing the event code procedure??? Is there a test we can run to confirm?I don't get it... it works for me.
We need help here from the rest of you reading this thread... install the code in Message #20 using the install instruction in Message #4, then type values 040114, 121514 and so on (where the numbers are mmddyy values with date delimiters in them) and tell us if those values are converted to real dates or not.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
On Error GoTo NothingInColumn
If Not Intersect(Target, Columns("A")) Is Nothing Then
[COLOR="#FF0000"]MsgBox "done1!!!!"[/COLOR]
For Each Cell In Intersect(Target, Intersect(Columns("A"), ActiveSheet.UsedRange))
If Not IsDate(Cell.Value) And Not Cell.Value Like "*[!0-9]*" Then
Cell.NumberFormat = "General"
Cell.Value = Format(Cell.Value, "0/00/00")
[COLOR="#FF0000"]MsgBox "done2!!!!"[/COLOR]
End If
Next
End If
NothingInColumn:
End Sub
I did that and nothing changed...What does that tell us?Add the lines in red
Code:Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range On Error GoTo NothingInColumn If Not Intersect(Target, Columns("A")) Is Nothing Then [COLOR=#ff0000]MsgBox "done1!!!!"[/COLOR] For Each Cell In Intersect(Target, Intersect(Columns("A"), ActiveSheet.UsedRange)) If Not IsDate(Cell.Value) And Not Cell.Value Like "*[!0-9]*" Then Cell.NumberFormat = "General" Cell.Value = Format(Cell.Value, "0/00/00") [COLOR=#ff0000]MsgBox "done2!!!!"[/COLOR] End If Next End If NothingInColumn: End Sub
I did that and nothing changed...What does that tell us?
Did you get the message boxes when you typed a number in Column A? If you get the message boxes then it definitely is recognizing it.Could it be possible that my spreadsheet isn't even recognizing the event code procedure??? Is there a test we can run to confirm?
No messages....The question was
Did you get the message boxes when you typed a number in Column A? If you get the message boxes then it definitely is recognizing it.
that's just it...i don't think it was ever firing...rick sent me instructions on how to create one since i had never done it before, so perhaps we both missed a step???then i would say the sheet isn't recognizing the code, only problem is i can't think why at present.
If i do think of any reason why it would stop after it was firing earlier then i'll post back.
that's just it...i don't think it was ever firing...rick sent me instructions on how to create one since i had never done it before, so perhaps we both missed a step???