Typing in dates

[ALLAROUNDOFFICE]

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?

 

[ALLAROUNDOFFICE]

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.

Could it be possible that my spreadsheet isn't even recognizing the event code procedure??? Is there a test we can run to confirm?

 

[MARK858]

Add the lines in red

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

 

[ALLAROUNDOFFICE]

Add the lines in red

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?

 

[MARK858]

I did that and nothing changed...What does that tell us?

The question was

Could it be possible that my spreadsheet isn't even recognizing the event code procedure??? Is there a test we can run to confirm?

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.

 

[ALLAROUNDOFFICE]

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.

No messages....

 

[MARK858]

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.

 

[ALLAROUNDOFFICE]

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

 

[Rick Rothstein]

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

Are you sure you right-click the correct tab in order to select Visual Basic from its popup menu? If you right-clicked the wrong tab, you would still get a code window to copy/paste into, but it would be the wrong one. The fact that you are not getting the MessageBoxes to appear means that the Column A you are typing into has no values in it... and that would happen only if you put the code in the wrong code window (or, possibly, that you are entering your values in the wrong worksheet)... I can think of no other reason what you would not see any MessageBoxes.

 

[ALLAROUNDOFFICE]

 

[MARK858]

When you are in the code window are any lines yellow?