automatically change the data entered into a cell to a specific format

[adscorpio]

I saw this done once, however the VBA code is locked and I cannot look at it. I have been dying to know how this was done. I am fairly certain it is VBA execute on enter, however I do not know for sure.

Here is what happens:

Enter value 6p, or 0600p or 1800 into cell, press enter, automatically changes to 6:00 PM

It does this for any time type value entered into the cells. If you just type 0730 it would change to 7:30 AM once you press enter or move to the next cell.

This was a scheduling tool that was put together and very VBA/Macro heavy. I am working on a schedule worksheet of my own and it would be nice to be able to utilize this so that the users are not required to use a colon when entering the time. I have tried just cell formatting but no matter what I make the cell format it will not recognize time entry unless it is entered as 6:00 (pm optional).

 

[Rick Rothstein]

I saw this done once, however the VBA code is locked and I cannot look at it. I have been dying to know how this was done. I am fairly certain it is VBA execute on enter, however I do not know for sure.

Here is what happens:

Enter value 6p, or 0600p or 1800 into cell, press enter, automatically changes to 6:00 PM

It does this for any time type value entered into the cells. If you just type 0730 it would change to 7:30 AM once you press enter or move to the next cell.

This was a scheduling tool that was put together and very VBA/Macro heavy. I am working on a schedule worksheet of my own and it would be nice to be able to utilize this so that the users are not required to use a colon when entering the time. I have tried just cell formatting but no matter what I make the cell format it will not recognize time entry unless it is entered as 6:00 (pm optional).

This event code is probably close to what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim T As String
  If Target.Count > 1 Then Exit Sub
  With Target
    If Not Intersect(Target, Range("D:G")) Is Nothing Then
      On Error GoTo CleanUp
      Application.EnableEvents = False
      T = .Value
      If T Like "*[aApP]" Then
        T = Replace(T, "a", " AM", , , vbTextCompare)
        T = Replace(T, "p", " PM", , , vbTextCompare)
      ElseIf T Like "*[AaPp][mM]" Then
        T = Left(T, Len(T) - 2) & " " & Right(T, 2)
      End If
      If Not IsDate(T) And InStr(T, ":") = 0 And Len(T) > 1 Then
        T = Left(T, InStr(T & " ", " ") - 3) & ":" & _
             Mid(T, InStr(T & " ", " ") - 2)
      End If
      T = WorksheetFunction.Trim(T)
      If IsDate(T) Then
        .Value = CDate(T)
      Else
        MsgBox "That is not a real time value!"
      End If
    End If
  End With
CleanUp:
  Application.EnableEvents = True
End Sub

Change the part I highlighted in red to the address for the range of cells you wish this functionality to be applied to.

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

 

[adscorpio]

You. Are. Awesome.

Thanks Rick, I should have come to this board sooner. The only piece I don't need was the msg box, but that's a cool trick, and easy enough to remove.

 

[GiGi5]

This event code is probably close to what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim T As String
  If Target.Count > 1 Then Exit Sub
  With Target
    If Not Intersect(Target, Range("D:G")) Is Nothing Then
      On Error GoTo CleanUp
      Application.EnableEvents = False
      T = .Value
      If T Like "*[aApP]" Then
        T = Replace(T, "a", " AM", , , vbTextCompare)
        T = Replace(T, "p", " PM", , , vbTextCompare)
      ElseIf T Like "*[AaPp][mM]" Then
        T = Left(T, Len(T) - 2) & " " & Right(T, 2)
      End If
      If Not IsDate(T) And InStr(T, ":") = 0 And Len(T) > 1 Then
        T = Left(T, InStr(T & " ", " ") - 3) & ":" & _
             Mid(T, InStr(T & " ", " ") - 2)
      End If
      T = WorksheetFunction.Trim(T)
      If IsDate(T) Then
        .Value = CDate(T)
      Else
        MsgBox "That is not a real time value!"
      End If
    End If
  End With
CleanUp:
  Application.EnableEvents = True
End Sub

Change the part I highlighted in red to the address for the range of cells you wish this functionality to be applied to.

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself.

How can I manipulate the code to only show hours and minutes? Say 1800 is 6:00, no seconds, no AM or PM. Thanks!