Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Location
    Cypress, California, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Twist on simplified TIME/DATE entry (2002)

    I know there are several macros for helping enter military time without the colon ":" but I need something a little different...

    I need a way to enter time only and end up with time and today's date -- something like TargetCell.Value + Today() -- BUT only if the user did not enter a date themselves.

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

    Re: Twist on simplified TIME/DATE entry (2002)

    As in your previous question, you can use the Worksheet_Change event.

    Just the date/time entry:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("A1:A10")).Cells
    If IsDate(oCell.Value) Then
    If Int(oCell.Value) = 0 Then
    oCell = oCell + Date
    End If
    End If
    Next oCell
    End If
    End Sub

    Combined with the "conditional formatting" from your other question (but see Steve's comment in <post#=465837>post 465837</post#>)

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    For Each oCell In Intersect(Target, Range("A1:A10")).Cells
    If IsDate(oCell.Value) Then
    If Int(oCell.Value) = 0 Then
    oCell = oCell + Date
    End If
    If Int(oCell.Value) = Date Then
    oCell.NumberFormat = "hh:mm AM/PM"
    Else
    oCell.NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
    End If
    Else
    oCell.NumberFormat = "General"
    End If
    Next oCell
    End If
    End Sub

Posting Permissions

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