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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Time stamp of unloading trucks in excel form
0
Discussion
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"
Also, to protect/unprotect sheets, just do something like Sheets("My Sheet").unprotect "password" and Sheets("My Sheet").protect "password"
don (rep: 1989) May 14, '19 at 10:42 pm
Answers
0
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.
Discussion
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!
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.
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: 4889) May 16, '19 at 9:18 pm
0
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!