time format in active sheet / page and target cells Free Excel Macros (VBA) | Excel Programs

'For online Excel courses click: https://www.excelsizeyeter.com/excelwithkenan.com/udemy_giris.php

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

On Error GoTo EndMacro
If Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
    Exit Sub
End If
If Target.Cells.Count > 1 Then
    Exit Sub
End If
If Target.Value = "" Then
    Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
    Select Case Len(.Value)
        Case 1 ' e.g., 1 = 00:01 AM
            TimeStr = "00:0" & .Value
        Case 2 ' e.g., 12 = 00:12 AM
            TimeStr = "00:" & .Value
        Case 3 ' e.g., 735 = 7:35 AM
            TimeStr = Left(.Value, 1) & ":" & _
            Right(.Value, 2)
        Case 4 ' e.g., 1234 = 12:34
            TimeStr = Left(.Value, 2) & ":" & _
            Right(.Value, 2)
        Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
            TimeStr = Left(.Value, 1) & ":" & _
            Mid(.Value, 2, 2) & ":" & Right(.Value, 2)
        Case 6 ' e.g., 123456 = 12:34:56
            TimeStr = Left(.Value, 2) & ":" & _
            Mid(.Value, 3, 2) & ":" & Right(.Value, 2)
        Case Else
            Err.Raise 0
    End Select
    .Value = TimeValue(TimeStr)
End If
End With
Application.EnableEvents = True
Exit Sub
EndMacro:
MsgBox "You did not enter a valid time"
Application.EnableEvents = True
End Sub

No	Macro Name
1	time format in active sheet / page and target cells
2	inserts and deletes annotations with the date and time in the active sheet / page
3	inserts the date and time when data was entered into the cells where data was entered on the active sheet / page as a comment
4	assigns date to the next line when data is entered in certain cells on the sheet / page
5	date format in active sheet / page and target cells
6	excel operators hk
7	status bar macro with a message waiting
8	backup
9	remove sheet / page protections
10	right mouse on active sheet / page menu
11	give background color by number on active sheet / page
12	inserts months as sheet / pages Specifies days in detail
13	and deleting formulas for formulated cells on a sheet / page as comments
14	adds months as sheet / pages and goes to that day
15	Adds months as a sheet / page and writes the days of the month and goes to the active month and day
16	records detailed formatted cells on the records sheet / page
17	specifies in detail how many formulas are in the book (on new sheet / page)
18	list formulas on new sheet / page
19	write the same cell in all cells on the active sheet / page
20	show file path list in detail fields of files file path etcâ € ¦

Excel Free Macros (VBA)

Time Format İn Active Sheet / Page And Target Cells

Online Excel Courses

Services

How can we help you?

Subscribe to our Youtube Channel?

Keywords