One-click VBA Macro for time?

[aherzog]

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.

 

[HalfAce]

"after editing, it changes from a "h:mm AM/PM" format to a "d/mm/yyyy h:mm:ss AM/PM" format."
In your code, change the line:
.Value = Now
to:
.Value = Time


"Is there a way to only have the macro work if the cell is empty?"
Yes. Add this line:
If Target <> "" Then Exit Sub
right below this line:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Hope it helps.

 

[aherzog]

Thanks! They both worked and the problem was solved with ease. You're awesome!

 

[aherzog]

Actually... unfortunately there is a problem =/

If I select more than one cell at a time (in any column, not just the ones assigned to the macro), then a "debug" message pops up for the formula you gave me. Not sure why that is? There are 12 columns in all on this worksheet, though the macro is supposed to only apply to two of them.

 

[HalfAce]

Which 2 columns would you like this to perform on, F & G, right? As it is now you have a range of 3 columns specified. (F, G & H)
Unless you have merged cells or something between columns G & H then we should specify a target intersect of Range F:G instead of F:H.
(I hope it's not merged cells. Merged cells is the devil when it comes to coding.)
Also, I'm not sure why you've limited the code to run only if the target count is 6 cells or less. (?) For something like this I would limit it to only run when a single cell is selected. (But again, this could be a merged cell thing if that's what is being used.)
Lastly, as you've likely already figured out, to use the selection change event you'll want to turn off Excel's option of moving the cursor/selection upon hitting the Enter key.

With all that in mind, a little rearranging of the code here seems to be working well for me. (Assuming I understand what you're really after and all the little details that may be involved.)

You might give this a try and see if it gets you closer.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 On Error Resume Next
 If Target <> "" Then On Error GoTo 0: Exit Sub
 If Target.Cells.Count > 6 Then Exit Sub
 If Intersect(Target, Range("F:H")) Is Nothing Then Exit Sub
 With Target
    .Value = Time
    .NumberFormat = "h:mm AM/PM"
 End With
End Sub

That help any?

 

[aherzog]

No, the cells are not merged. In fact, I have revised the Macro code since this post to read as "G:H" as those are the only two columns I want this to apply to - my apologies for any confusion I may have caused. And, as for the limit to 6... to be quite honest, this is the first experience I've ever had creating a macro and I was doing my best to learn from making a Frankenstein of other codes =p And! Great point about the Enter key also adding time to the next cell even if my mouse cursor isn't clicking the cell.

I tried the new Macro and the error message has disappeared when I select more than one cell, however, the cell still calculates time when I hit the Enter button. Regardless, I think the problem has been solved so thank you. =) And as I mentioned, this is the first macro I have ever had any experience with so if you want to teach me a thing or too I wouldn't mind that either. Thanks!

 

[HalfAce]

however, the cell still calculates time when I hit the Enter button.

You mean it's entering the time in the new cell the cursor moves to when hitting Enter? (For example down?)
If so, you can go into your excel options and look for/uncheck the 'Move On Enter' checkbox.

Nice going for your first time coding!
Glad it's helping.