I have to make a form that track the time of starting and finishing of certain process (unloading trucks). So i would like to know, can something like this be done within user form in excel.
I have to make a form that track the time of starting and finishing of certain process (unloading trucks). So i would like to know, can something like this be done within user form in excel.
Of course that could be done with a UserForm. The better question, it seems to me, is whether it can be done without one and the answer to that is yes, too. Here is the code. It must be pasted into the code module of the worksheet on which you want the action.
Private Enum Nws ' worksheet navigation
' change the assigned values as required
' 13 May 2019
NwsFirstDataRow = 3
NwsStart = 3
NwsEnd = 4
End Enum
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' 13 May 2019
Dim Rl As Long
Dim Tgt As Variant
With Target
If .Row >= NwsFirstDataRow Then
Tgt = .Value
Rl = Cells(Rows.Count, NwsStart).End(xlUp).Row
Select Case .Column
Case NwsStart
If .Row <= Rl + 1 Then
Cancel = Not IsDate(Cells(.Row, NwsEnd))
End If
Case NwsEnd
If .Row <= Rl Then
Cancel = IsDate(Cells(.Row, NwsStart))
End If
End Select
End If
If Cancel Then .Value = Now()
End With
End Sub
In this example, there are header captions in rows 1 and 2. Therefore row 3 is the first data row. Therefore the enumeration NwsFirstDataRow is assigned a value of 3. You might change this value to 2 or 4, depending upon your worksheet design.
Similarly, my example records the start time in column C which, in VBA parlance, is column 3. Therefore the enumeration NwsStart is assigned the value 3. You could have the time to start loading in column G and therefore make NwsStart = 7. Same for NwsEnd, so long as Start and End are in different columns.
With that said you are now ready to record times. Simply double-click on the cell in which you wish to write the current time. The double-click is without effect in columns other than NwsStart or NwsEnd, in rows above NwsFirstDataRow and below the last used row in column NwsStart, except for the cell immediately below the last recorded start time, and in the NwsEnd column if no start time has been recorded. This means, among other things, that an already recorded time can be over-written. However, the exact rules might be tweaked to meet your specific requirements.
The system is demonstrated in the attached workbook.
Thanky you very much!
This works excellent!
Do you know maybe how can i protect this entries, so that once entered time on click cannot be adjusted anymore.
Big thanks again!