Record entry and exit times with Barcodes in Excel
HTML-код
- Опубликовано: 12 сен 2024
- Record entry and exit times with Barcodes in Excel. With a barcode scanner record the in and out times on an Excel spread sheet.Check out my templates page for free and purchased templates www.easyexcela...
Check out my online training www.easyexcelanswers.com/courses.html
For more help visit my website www.easyexcelan... or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
Click for online Excel Consulting www.calendly.co...
Excel one-on-one on-line training available. Email me to arrange.
I am able to provide online help on your computer at a reasonable rate.
www.amazon.com...
Check out my next one-hour Excel Webinar
www.crowdcast....
I use a Blue condensor Microphone to record my videos, here is the link
amzn.to/37gyyGa
Check out Crowdcast for creating your webinars
app.linkmink.c...
If you need to buy Office 2019 follow
amzn.to/2VX5dv8
I use Tube Buddy to help promote my videos
Check them out
www.Tubebuddy....
Follow me on Facebook
/ easyexcel.answers
TWEET THIS VIDEO • Record entry and exit ...
Follow me on twitter
easyexcelanswers
IG @barbhendersonconsulting
You can help and generate a translation to you own language
www.youtube.com...
*this description may contain affiliate links. When you click them, I may receive a small commission at no extra cost to you. I only recommend products and services that I've used or have experience with.
Templates with code are available for purchase for $50 USD
www.easyexcela...
code
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
Call access
Application.EnableEvents = True
End If
End Sub
Sub access()
Dim barcode As String
Dim rng As Range
Dim foundVal As Range
Dim diff As Double
Dim rownumber As Long
barcode = ActiveSheet.Cells(2, 1)
If barcode (symbol for not equal"" Then
Set rng = ActiveSheet.Range("a4:a150").Find(What:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
ActiveSheet.Range("a4:a1005").Find("").Select
ActiveCell.Value = barcode
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
ActiveSheet.Cells(2, 1) = ""
Else
rownumber = rng.Row
ActiveSheet.Range(Cells(rownumber, 1), Cells(rownumber, 10)).Find("").Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
ActiveSheet.Cells(2, 1) = ""
End If
End If
ActiveSheet.Cells(2, 1).Select
End Sub
Thank you so much for watching my video. In the code I have included in the description of video, I am not able to include square brackets . So when you see the (symbol for not equal to) replace it with for the code to run.
Great video. Although I tried to scan a bar code but it did not work, only worked when I entered the value manually. How to i enable the bar code scan to work.
Cell phone barcode scanner by using scan it to office app
Your all vedio very helpful for us
Once again thank you so much
thank you soo much this is what ive been looking for
Glad I could help
Dear Mam,
Thank you so much for such a wonderful tutorial. it really helped me a lot. I need one more help from your side. Actually i want to display in and out time with date in a separate column. could you please guide me how to do that?
Thank you so much for such a wonderful and useful tutorial . it really helped me . based on your guide line I have try with barcode scanned but after barcode scanned we have to hit the enter key then only time stamp showing , can we make once scanned the barcode without hit the enter key barcode no should be go to down as order line .
normally you can set the barcode scanner to press enter once it has scanned the number. Check the settings on the scanner
Hi! This is super helpful thus far and very close to what I've been looking for. I watched a couple of your other videos as well regarding calculating the time, but I'm struggling to apply the same concept here. As I am dealing with over 4000 students, I need the sheet to stay in the same format you have in this video, but to also calculate the total hours they have made up (clocked in and out) at the end. Any suggestions or help you can provide?
the code on this video ruclips.net/video/hcL3q-kfUbY/видео.html might be helpful
Awesome Video, Just wondering if there is a way time stamp the same ID after it populate the info In B and C to go down to the next row instead of entering in D and E and going to the next collum thanks!
you could have the name at the top and the in and out times below it. Just the way that you set up your sheet.
Is it possible to have 2 inputs to add an employee name as well? Ideally just use one column of IN/OUT and also have a column for the time between IN/OUT. That way an employee can log them selves to a job, have lunch and log back into a job and the time spent on it will be collected.
Break times is something I am working on
This is Extremely helpful to track employees time!
I have one query, can the employee code/ID be highlighted when timing in/out?
rownumber = rng.Row
rownumber.Interior.Color = RGB(255, 255, 0)
and then at the end
rownumber.interior.color =xlnone
@@BarbHendersonconsulting Thanks for the swift response. Unfortunately I didn't get it to work. Below is the code in use. It was tweaked a bit to suit our needs. Your guidance is appreciated!
Sub access()
Dim barcode As String
Dim rng As Range
Dim rownumber As Long
barcode = ActiveSheet.Cells(1, 1)
If barcode "" Then
Set rng = ActiveSheet.Range("b3:b150").Find(What:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
Range("b3").EntireRow.Insert
Range("b3").Select
ActiveCell.Value = barcode
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Time
ActiveCell.NumberFormat = " h:mm AM/PM"
ActiveSheet.Cells(1, 1) = ""
Else
rownumber = rng.Row
ActiveSheet.Range(Cells(rownumber, 1), Cells(rownumber, 100)).Find("").Select
ActiveCell.Value = Time
ActiveCell.NumberFormat = " h:mm AM/PM"
ActiveSheet.Cells(1, 1) = ""
End If
End If
ActiveSheet.Cells(1, 1).Select
End Sub
Thank u somuch👍
Welcome 😊
Thanks, this video helps me a lot. But can you assist me how to create a separate sheet for the barcode?
Take a look at this one, I am not sure this is what you need
ruclips.net/video/g89fzCcPa9Q/видео.html
@@BarbHendersonconsulting Thank you! This is what I’m looking for 👍
@@arifahbahri7757 Happy that I could help!
hi, thanks for the sharing. I want to know is it possible if i use barcode scanner instead of typing the id?
yes, it is designed for a barcode scanner. I personally do not own one.
Hello Barb, is there a possibility to make this work for multiple days? My workers have to scan in and out every day. So something like a new sheet will open every day. Otherwise I will have 10 lines at the end of the week
Take a look at this video, I think this is what you need ruclips.net/video/hcL3q-kfUbY/видео.html
Hi, why active sheet range got different? One is a4:a150 and another one is a4:a1005
they should both be the same ranges, just a slip in my typing ability😀
Hey Barb. Great Tutorial, thank you. How would I change the code so rather than a new 'in time and date' in column D, it simply clears column C and overwrites the data in column B?
replace ActiveSheet.Range(Cells(rownumber, 1), Cells(rownumber, 10)).Find("").Select
with
ActiveSheet.Cells(rownumber, 3).clearcontents
ActiveSheet.Cells(rownumber, 3).select
@@BarbHendersonconsulting Thank you, (and for the amazingly quick reply 🙂) I think I'm almost there however, that code only overwrites column C data rather than deleting B & C and adding the new data in Column B again. Im not sure what I need to add but basically, if data is already in column C, Delete B & C and add new data to Column B. Is this possible?
Hello! May I ask if I want to stop the excel sheet to accept entry after a number of scans?
you must set up a column where they are no longer able to enter. for example if it reach column "H" then it will send an error message and exit sub
Hi Thanks for the code. Any clue why my code is not active and after every value on A2 only when I hit run macro does it return a time stamp. Any thing to do with my sheet settings?
If you install this code on your worksheet it will call the other code when a change is made. "Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
Call access
Application.EnableEvents = True
End If
End Sub
How can i code this to calculate total in and out times?
ruclips.net/video/EG2aIkh4LyU/видео.html
How to lock the cell after you enter the barcode so that employee cannot edit the logs.Thanks
check out this video, ruclips.net/video/DlSl5Je-4HE/видео.html I do not know if it will work because you have the code on the worksheet to call the other program
Can this be done in c programming
As far as I know VBA is the only code that works in Excel. The code is located in the description of the video. Feel free to copy and use it
Hi Mdm,
How to I create a new row from an ID who had checked out previously instead of reflecting in the same row? Thank you.
What I have done in that instance is add additional columns for in and out. So that you are able to see all the in and outs for one day
how can I buy this template?
email me at easyexcelanswers@gmail.com
Do you have a downloadable workbook as an example?
The code is located in the description of the video
@@BarbHendersonconsulting thank you. Am having challenges getting the code to funciton. Was hoping there was a downloadable workbook that had the code in it that I could study and modify to our needs.
what is "list separator" ?
I do not understand the question.
The macro only works when I "Run Sub" manually from code view. How can I get the macro to run automatically once a number is entered into the cell?
have you entered
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
Call access
Application.EnableEvents = True
End If
End Sub
on the sheet?
@@BarbHendersonconsulting Private Sub Worksheet_Change(ByVal Target As Range)
'Application.EnableEnvents = True
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
'Application.EnableEnvents = False
Call access
Application.EnableEvents = True
End If
If ScannerTrigger = True Then
'If the barcode scanner trigger is pressed
Workbook.SetFocus
End If
End Sub
I also removed the "If Scanner..." and still doesnt work
How would you record a barcode entry that already exists in the data as a new row?
you would have to set it so that then it would find the next empty row and enter it there.
@@BarbHendersonconsulting Thank you so much for the video, but if the entry is already recoded twice how I can move the same entry to be recoded to the new row again?
this is exactly what i need, but i dont know how to start. it not working in my excel
Check out this video ruclips.net/video/AByFH0TN53M/видео.html
I have everything working but I only need it to capture 1 check in and 1 check out and have the code stop working so I dont get an error. I cannot figure how to do that in the Code. Can someone help?
I think that this is the video that you need ruclips.net/video/jOeEPO8xwr8/видео.html
@@BarbHendersonconsulting Thank you Barb! I have tried running that code but I need to check its Barcode Entry Column to A2 and have the code read from there. I am missing something. In this video the sheet calls calls the module but in the other video no module is called. Is this required?
@@JK-je8no you must also insert the code on the worksheet, similar to the other one. When data is placed in the cell it will automatic run
Madam video very useful But i need department wise pls Help me Madam
contact me at easyexcelanswers@gmail.com for custom work
I will run the code it is working but it is not working automatically I have to run macro everytime Kindly guide
did you install this code on the worksheet?
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then
Call access
Application.EnableEvents = True
End If
End Sub
@@BarbHendersonconsulting yes but it give error of ambiguous name detected: change_worksheet
Is this applicable in Google sheet?
No, google sheets works on Javascript and Excel is on VBA.
Can you help me on how to create it in javascript?
Hey Barb, great video! I have one question I bet you can help me out with
I had this code running (tweaked it a bit for my needs) and a week or so later I went back into my sheet and all the sudden I'm getting runtime error 91 : Object variable or with block variable not set
I'm not sure why I'm all the sudden getting this error message as the code has been working and I didn't change anything. I attached most of it below (I have a function for each day of the week but I'll only attach the first part so you can get the idea)
I'd appreciate any advice, thanks!
Private Sub worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
Call monday
Application.EnableEvents = True
End If
If Not Intersect(Target, Me.Range("F1")) Is Nothing Then
Call tuesday
Application.EnableEvents = True
End If
If Not Intersect(Target, Me.Range("K1")) Is Nothing Then
Call wednesday
Application.EnableEvents = True
End If
If Not Intersect(Target, Me.Range("P1")) Is Nothing Then
Call thursday
Application.EnableEvents = True
End If
If Not Intersect(Target, Me.Range("U1")) Is Nothing Then
Call friday
Application.EnableEvents = True
End If
End Sub
Sub monday()
Dim barcode As String
Dim rng As Range
Dim foundval As Range
Dim diff As Double
Dim rownumber As Long
barcode = ActiveSheet.Cells(1, 1)
If barcode "" Then
Set rng = ActiveSheet.Range("b5:b500").Find(what:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
ActiveSheet.Cells(1, 1) = ""
Else
rownumber = rng.Row
ActiveSheet.Range(Cells(rownumber, 1), Cells(rownumber, 4)).Find("").Select
That makes no sense that you would get a new error message.
@@BarbHendersonconsulting Does the code look okay?
@@user-ye3bs6fl6b I could not see anything that look wrong!