VBA code for calculating time

[James__S]

Hi
I select a cell and press the button and it inserts date and time in cell
I insert the start date and time in the ranges for F Column
I then insert a finish date and time in the range for G Column

I use this VBA code below with an Active X Control button

Private Sub CommandButton1_Click()


ActiveCell = Now()


End Sub

1. How do i limit the code to only use the following ranges
F5:F32 & G5:G32
F35:F60 & G35:G60
2. If there is a start date and time in say cell F5 and a finish date and time in G5 can cell H5 then calculate the difference in minutes
Example: [start cell F5] 01-Jan-2018 04:22 [finish cell G5] 03-Jan-2018 14:52 [H5 - minutes between the 2 dates]
H5 will be blank unless F5 & G5 have a date and time
Is it possible to add these 2 requests to the code above

Thanks

 

[FormR]

Hi, for 1 you could try something like:

Private Sub CommandButton1_Click()
If Not Intersect(ActiveCell, Range("F5:G32,F35:G60")) Is Nothing Then
  ActiveCell = Now()
End If
End Sub

For 2 you could pre-populate those cells with a formula, for example, in H5.

=IF(COUNT(F5:G5)=2,(G5-F5)*24*60,"")

 

[James__S]

Thanks FormR
Works well

Can the following be added to the code
Once an entry is inserted in say F5 & G5 and H5 is calculated can these 3 cells be locked so they cannot be changed
This would be for the entire range as cells have entries

Thanks

 

[FormR]

Once an entry is inserted in say F5 & G5 and H5 is calculated can these 3 cells be locked so they cannot be changed

Hi, You would need to initially set the locked property of the cells you want to allow to be changed to false and protect the worksheet. Then in your code you would need to unprotect the sheet, set the locked property of the cells in question to True and then re-protect the sheet afterwards.

 

[James__S]

Thank you