Time stamp of unloading trucks in excel form


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.



Please don't forget to select the answer that worked for you! Just click the Select Answer button at the bottom of the desired answer.

Also, to protect/unprotect sheets, just do something like Sheets("My Sheet").unprotect "password" and Sheets("My Sheet").protect "password"
don (rep: 1960) May 14, '19 at 10:42 pm
Add to Discussion


Selected Answer

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.



Hello again. :)

I would like to know is it posible to include any targeted row bellow row number 3 into time stamping?

My table looks like this:
i got list of trucks that are planned for tommorow. One row of data describes one truck.
For example if truck in row 10 comes  in warehouse before truck in row 6 i cannot click in cell C10 to time stamp gate in of the first truck (ROW10).

Thank you in advance!
Lsavic (rep: 2) May 16, '19 at 7:28 pm
I'm glad you liked to suggestion. Both your requests for amendment are possible. Neither one is particularly difficult. But folow-up questions like yours are both endless in nature and messy to answer. Therefore I don't entertain follow-ups. Please post a copy of your actual workbook in a new question and ask for the changes you would like.
Note that one of the key issues in this is whether or not trucks are loaded in the sequence in which they are listed. If the Start time is always in the last row all others are easy to protect. Make sure that this information is provided in your question, as opposed to here where it is given in the discussion of the answer.
Variatus (rep: 4258) May 16, '19 at 9:18 pm
Add to Discussion

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!


Answer the Question

You must create an account to use the forum. Create an Account or Login