Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Convert Date Entry (Excel 2003, SP2)

    I'm using code from here http://www.cpearson.com/excel/DateTimeEntry.htm to allow users to enter dates without delimiters. But I need to modify it so that if they DO enter delimiters, it will still work correctly. I tried entering this:

    If InStr(1, Target.Value, "/") Or InStr(Target.Value, "-") Then GoTo Checkdate

    that jumps over the code if they use delimiters, but it only works the first time, while the cell format is set to "General." As soon as one date is entered, Excel changes the format to "Date," and then, if the user tries to go back and change a date (which happens often), it breaks the code. I tried setting the cell back to "General" on selection, but if the user then exits the cell without making a change, it stays General.

    It seems like this should have an easy fix, but I can't seem to see it. Any ideas? My complete code is below, my changes in red. Many thanks!

    --Karyl

    <pre>Option Explicit
    <font color=red>Public bGeneral As Boolean</font color=red>

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim DateStr As String

    On Error GoTo EndMacro

    Select Case Target.Address
    Case "$P$3", "$G$6", "$H$6", "$I$6", "$J$6", "$K$6", "$C$20", "$D$20", "$E$20", _
    "$F$20", "$G$20", "$H$20", "$I$20", "$J$20", "$K$20", "$B$47"

    'Convert date code from
    'Pearson Software Consulting, LLC
    'http://www.cpearson.com/excel/DateTimeEntry.htm

    'If multiple cells are involved, then exit the sub
    If Target.Cells.Count > 1 Then Exit Sub

    'If the field is blank, exit the sub
    If Target.Value = "" Then Exit Sub

    <font color=red>'If bGeneral = False Then Target.NumberFormat = "General" [DOESN'T FIX THE PROBLEM]

    If InStr(1, Target.Value, "/") Or InStr(Target.Value, "-") Then GoTo Checkdate</font color=red>
    Application.EnableEvents = False
    With Target
    If .HasFormula = False Then
    Select Case Len(.Formula)
    Case 4 ' e.g., 9298 = 2-Sep-1998
    DateStr = Left(.Formula, 1) & "/" & _
    Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
    Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
    DateStr = Left(.Formula, 1) & "/" & _
    Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
    Case 6 ' e.g., 090298 = 2-Sep-1998
    DateStr = Left(.Formula, 2) & "/" & _
    Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
    Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
    DateStr = Left(.Formula, 1) & "/" & _
    Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
    Case 8 ' e.g., 09021998 = 2-Sep-1998
    DateStr = Left(.Formula, 2) & "/" & _
    Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
    Case Else
    Err.Raise 0
    End Select
    .Formula = DateValue(DateStr)
    End If
    End With
    Application.EnableEvents = True
    Case Else
    Exit Sub
    End Select

    Exit Sub

    <font color=red>Checkdate:
    If IsDate(Target.Value) Then
    Target.NumberFormat = "m/d/yyyy"
    Exit Sub
    End If</font color=red>

    EndMacro:
    MsgBox "You did not enter a valid date."
    Application.EnableEvents = True

    End Sub


    <font color=red>Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Select Case Target.Address
    Case "$P$3", "$G$6", "$H$6", "$I$6", "$J$6", "$K$6", "$C$20", "$D$20", "$E$20", _
    "$F$20", "$G$20", "$H$20", "$I$20", "$J$20", "$K$20", "$B$47"

    'If multiple cells are involved, then exit the sub
    If Target.Cells.Count > 1 Then Exit Sub

    'If the field is blank, exit the sub
    If Target.Value = "" Then Exit Sub

    If Target.NumberFormat = "General" Then
    bGeneral = True
    Else
    bGeneral = False
    End If

    Case Else
    Exit Sub
    End Select

    End Sub</font color=red> </pre>


  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Convert Date Entry (Excel 2003, SP2)

    Hi,
    Does this work for you:

    <pre>Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim DateStr As String

    On Error GoTo EndMacro

    Select Case Target.Address
    Case "$P$3", "$G$6", "$H$6", "$I$6", "$J$6", "$K$6", "$C$20", "$D$20", "$E$20", _
    "$F$20", "$G$20", "$H$20", "$I$20", "$J$20", "$K$20", "$B$47"

    'Convert date code from
    'Pearson Software Consulting, LLC
    'http://www.cpearson.com/excel/DateTimeEntry.htm

    'If multiple cells are involved, then exit the sub
    If Target.Cells.Count > 1 Then Exit Sub

    'If the field is blank, exit the sub
    If Target.Value = "" Then Exit Sub

    If InStr(Target.NumberFormat, "yy") Then
    If InStr(1, Target.Text, "/") Or InStr(Target.Text, "-") Then GoTo Checkdate
    End If
    Application.EnableEvents = False
    With Target
    If .HasFormula = False Then
    Select Case Len(.Formula)
    Case 4 ' e.g., 9298 = 2-Sep-1998
    DateStr = Left(.Formula, 1) & "/" & _
    Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
    Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
    DateStr = Left(.Formula, 1) & "/" & _
    Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
    Case 6 ' e.g., 090298 = 2-Sep-1998
    DateStr = Left(.Formula, 2) & "/" & _
    Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
    Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
    DateStr = Left(.Formula, 1) & "/" & _
    Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
    Case 8 ' e.g., 09021998 = 2-Sep-1998
    DateStr = Left(.Formula, 2) & "/" & _
    Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
    Case Else
    Err.Raise 0
    End Select
    .Formula = DateValue(DateStr)
    End If
    End With
    Application.EnableEvents = True
    Case Else
    Exit Sub
    End Select

    Exit Sub

    Checkdate:
    If IsDate(Target.Value) Then
    Target.NumberFormat = "m/d/yyyy"
    Exit Sub
    End If

    EndMacro:
    MsgBox "You did not enter a valid date."
    Application.EnableEvents = True

    End Sub
    </pre>


    Regards,
    Rory
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Date Entry (Excel 2003, SP2)

    I think you'll have to choose - either let users enter a date as a date (my preference, no code needed), or instruct them to enter a number without delimiters (requiring Pearson's event code). Don't try to mix the two.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Date Entry (Excel 2003, SP2)

    I don't have XL2003, but in XL2K if you enter a date that Excel recognizes as a date it is converted to an Excel date value before the change event fires and the code can not see the delimiters. My experience says you can not mix the two.
    Legare Coleman

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert Date Entry (Excel 2003, SP2)

    Your version works for me for dates entered with delimiters, but produces incorrect results for dates entered without delimiters if the cell is already formatted as a date.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Convert Date Entry (Excel 2003, SP2)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> You're quite right - not enough testing on my part.
    I can't see a way around it (at least not without setting a keyboard hook whenever those cells are selected!) so I would agree that it's probably best just to either not do this, or tell the users they can't use shortcut methods once a date has been entered. (Given the limitations of the routine anyway, I don't think that would be a big deal)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Date Entry (Excel 2003, SP2)

    I figured out a method that seems to work. It LOOKS funny when you select a cell into which you've already entered a date, but it converts back as soon as you change to another cell (or save). Assuming users will only be selecting completed date cells to make a change, anyway, I think it will be okay. (But if you have a method that will suppress the display of .formula, please pass it on.)

    <pre>Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim DateStr As String

    On Error GoTo EndMacro

    If Application.Intersect(Target, Range("PDates, ODates, MDates")) Is Nothing Then
    Exit Sub
    End If

    'Convert date code from
    'Pearson Software Consulting, LLC
    'http://www.cpearson.com/excel/DateTimeEntry.htm

    'If multiple cells are involved, then exit the sub
    If Target.Cells.Count > 1 Then Exit Sub

    'If the field is blank, exit the sub
    If Target.Value = "" Then Exit Sub

    'Modification to allow for standard date entry
    If InStr(1, Target.Value, "/") Or InStr(Target.Value, "-") Then GoTo Checkdate

    Application.EnableEvents = False
    With Target
    If .HasFormula = False Then
    Select Case Len(.Formula)
    Case 4 ' e.g., 9298 = 2-Sep-1998
    DateStr = Left(.Formula, 1) & "/" & _
    Mid(.Formula, 2, 1) & "/" & Right(.Formula, 2)
    Case 5 ' e.g., 11298 = 12-Jan-1998 NOT 2-Nov-1998
    DateStr = Left(.Formula, 1) & "/" & _
    Mid(.Formula, 2, 2) & "/" & Right(.Formula, 2)
    Case 6 ' e.g., 090298 = 2-Sep-1998
    DateStr = Left(.Formula, 2) & "/" & _
    Mid(.Formula, 3, 2) & "/" & Right(.Formula, 2)
    Case 7 ' e.g., 1231998 = 23-Jan-1998 NOT 3-Dec-1998
    DateStr = Left(.Formula, 1) & "/" & _
    Mid(.Formula, 2, 2) & "/" & Right(.Formula, 4)
    Case 8 ' e.g., 09021998 = 2-Sep-1998
    DateStr = Left(.Formula, 2) & "/" & _
    Mid(.Formula, 3, 2) & "/" & Right(.Formula, 4)
    Case Else
    Err.Raise 0
    End Select
    .Formula = DateValue(DateStr)
    End If
    End With
    Application.EnableEvents = True

    Exit Sub

    Checkdate:
    If IsDate(Target.Value) Then Exit Sub

    EndMacro:
    MsgBox "You did not enter a valid date."
    Application.EnableEvents = True

    End Sub</pre>


    <pre>Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Variables set in Module1

    'Change it back to a date if the user didn't make any changes
    'Also added to Workbook_BeforeSave Event

    If bDate = True Then
    Range(strDate).NumberFormat = "m/d/yyyy"
    bDate = False
    End If

    'Convert to General if cell is a date (so that convert code will work)

    If Target.NumberFormat = "m/d/yyyy" Then
    bDate = True
    strDate = Target.Address
    Target.NumberFormat = "General"
    End If

    End Sub</pre>


  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Date Entry (Excel 2003, SP2)

    What happens if you select one of the cells with something in it and then exit the cell without changing anything?
    Legare Coleman

  9. #9
    2 Star Lounger
    Join Date
    Jul 2004
    Location
    Sumner, Washington, USA
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert Date Entry (Excel 2003, SP2)

    The code in the Selection_Change event converts it back to date format. So they SEE the .formula when they enter the cell, but it converts back to the original date when they click somewhere else. When you enter a cell, if it is in date format, it changes it back to general and toggles the bDate variable to True. When you enter the next cell, if bDate is true, it changes the previous cell back to a date and sets bDate to False. It seems to work okay if you select a range, as well.

    <pre>'Change it back to a date if the user didn't make any changes
    If bDate = True Then
    Range(strDate).NumberFormat = "m/d/yyyy"
    bDate = False
    End If</pre>



    <pre>'Convert to General if cell is a date (so that convert code will work)
    If Target.NumberFormat = "m/d/yyyy" Then
    bDate = True
    strDate = Target.Address
    Target.NumberFormat = "General"
    End If</pre>



    --Karyl

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •