VB 44
Excel VBA hhmm vs hh:mm By mike on 28th March 2021 03:58:36 PM
  1. http://www.vbaexpress.com/kb/getarticle.php?kb_id=75
  2.  
  3. 1. Open the workbook in which you wish to use the code.
  4. 2. Right-click the sheet tab of the sheet on which you wish the code to run, and choose View Code.
  5. 3. Copy the code above and paste it into the worksheet module window at the right of the Visual Basic Editor (VBE).
  6. 4. Redefine the range where the code should work on by adjusting the the line with intersect
  7.  
  8. ---------------------------------------------
  9.  
  10. Option Explicit
  11.  
  12. Private Sub Worksheet_Change(ByVal Target As Range)
  13.      
  14.     If Target.Cells.Count > 1 Then Exit Sub
  15.      
  16.      'Define the range where you want the code to work (our example is "C:G").
  17.      'Change within the " marks
  18.     If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
  19.      
  20. On Error GoTo errHandler:
  21.      
  22.     With Target
  23.         If IsNumeric(.Value) Then
  24.             Application.EnableEvents = False
  25.             Select Case .Value
  26.             Case 0
  27.                 .NumberFormat = "hh:mm"
  28.             Case 1 To 99
  29.                 .Value = TimeSerial(0, .Value, 0)
  30.                 .NumberFormat = "hh:mm"
  31.             Case 100 To 2399
  32.                 .Value = TimeSerial(Int(.Value / 100), .Value Mod 100, 0)
  33.                 .NumberFormat = "hh:mm"
  34.             Case 10000 To 235959
  35.                 .Value = TimeSerial(Int(.Value / 10000), _
  36.                 Int((.Value Mod 10000) / 100), .Value Mod 100)
  37.                 .NumberFormat = "hh:mm:ss"
  38.             Case 240000 To 245959
  39.                 .Value = TimeSerial(0, Int((.Value Mod 10000) / 100), .Value Mod 100)
  40.                 .NumberFormat = "hh:mm:ss"
  41.             Case Else
  42.             End Select
  43.         End If
  44.     End With
  45. errHandler:
  46.     Application.EnableEvents = True
  47. End Sub
  48.  
  49. ---------------------------------------------

Hasta la pasta! is for source code and general debugging text.

Login or Register to edit, delete and keep track of your pastes and more.

Raw Paste

Login or Register to edit or fork this paste. It's free.