I have a spreadsheet that tracks the times when my technicians arrive and depart from service calls. The idea is that each row contains several different categories, including two of which are Arrival Time (Column F) and Departure Time (Column G). When they call in I have to manually enter these times and they are in the "h:mm" format.
When they arrive, they call in to the store and I have to enter the time manually (I know I could also use ctrl+shift+; but I also consider that manual because I have to do it so frequently). I wanted a macro where I could literally just click the empty cell once and it automatically entered the "now" time. This is the macro I have:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 6 Then Exit Sub
If Not Intersect(Target, Range("F:H")) Is Nothing Then
With Target
.Value = Now
.NumberFormat = "h:mm AM/PM"
End With
End If
End Sub
There are two problems with this. First, if I want to edit the time already entered then I double click on the cell and after editing, it changes from a "h:mm AM/PM" format to a "d/mm/yyyy h:mm:ss AM/PM" format. Second issue is when I click a cell with an existing time already in it from the first time I used the macro (which usually happens by accident), then it automatically replaces it with the new current time. Is there a way to only have the macro work if the cell is empty? Or any other suggestions to combat this problem? Thanks.
When they arrive, they call in to the store and I have to enter the time manually (I know I could also use ctrl+shift+; but I also consider that manual because I have to do it so frequently). I wanted a macro where I could literally just click the empty cell once and it automatically entered the "now" time. This is the macro I have:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 6 Then Exit Sub
If Not Intersect(Target, Range("F:H")) Is Nothing Then
With Target
.Value = Now
.NumberFormat = "h:mm AM/PM"
End With
End If
End Sub
There are two problems with this. First, if I want to edit the time already entered then I double click on the cell and after editing, it changes from a "h:mm AM/PM" format to a "d/mm/yyyy h:mm:ss AM/PM" format. Second issue is when I click a cell with an existing time already in it from the first time I used the macro (which usually happens by accident), then it automatically replaces it with the new current time. Is there a way to only have the macro work if the cell is empty? Or any other suggestions to combat this problem? Thanks.