Allow information to be added when barcode is scanned in Excel
HTML-код
- Опубликовано: 23 авг 2024
- Allow information to be added when barcode is scanned in Excel. Give users the opportunity to enter information. Templates with code are available for purchase for $50 USD www.easyexcela...
For more help visit my website www.easyexcelanswers.com or email me at easyexcelanswers@gmail.com.
Contact me regarding customizing this template for your needs.
Click for online Excel Consulting www.calendly.co...
I am able to provide online help on your computer at a reasonable rate.
www.amazon.com...
I use a Blue condenser 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 • Allow information to b...
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 including code are available for $50 USD
code
Sub access()
Dim barcode As String
Dim rng, rng1 As Range
Dim rownumber As Long
Dim cell
Dim name As String
Dim Total As Double
Dim Timein As Date
Dim Timeout As Date
barcode = ActiveSheet.Cells(2, 1)
If barcode (does not equal) "" Then
Set rng = ActiveSheet.Columns("a:a").Find(What:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
restart:
ActiveSheet.Columns("a:a").Find("").Select
Set rng1 = Sheet2.Columns("a:a").Find(What:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng1 Is Nothing Then
With UserForm1
.Width = 300
.Height = 150
.Show
End With
Exit Sub
Else
ActiveSheet.Range("a5:a1005").Find("").Select
ActiveCell.Value = barcode
name = rng1.Offset(0, 1).Value
ActiveCell.Offset(0, 1).Value = name
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "d/m/yyyy h:mm AM/PM"
ActiveSheet.Cells(2, 1) = ""
End If
GoTo ende
Else
'rng.Select
rownumber = rng.Row
If Cells(rownumber, 4) (does not equal) "" Then GoTo restart
ActiveSheet.Cells(rownumber, 1).Select
barcode = ActiveCell.Value
ActiveCell.Offset(0, 3).Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "m/d/yyyy h:mm AM/PM"
Timein = CDate(Cells(rownumber, 3).Value)
Timeout = CDate(Cells(rownumber, 4).Value)
Total = TimeValue(Timeout) - TimeValue(Timein)
Debug.Print Total
Debug.Print Format(Total, "hh:mm:ss")
Cells(rownumber, 5).NumberFormat = "hh:mm:ss"
Cells(rownumber, 5).Value = Total
Debug.Print "Number of hours = " & Total * 24
'call emailconf
ActiveSheet.Cells(2, 2) = ""
End If
ActiveSheet.Cells(2, 1) = ""
'
End If
ende:
ActiveSheet.Cells(2, 1).Select
End Sub
Code for userform
Private Sub CommandButton1_Click()
Dim barcode As String
Dim Aname, Email As String
barcode = Sheet1.Range("A2").Value
Aname = TextBox1.Value
Email = TextBox3.Value
'enter information on first sheet
Sheet1.Activate
ActiveSheet.Range("a5:a1005").Find("").Select
ActiveCell.Value = barcode
ActiveCell.Offset(0, 1).Value = Aname
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "m/d/yyyy h:mm:ss AM/PM"
'record information on second sheet
Sheet2.Activate
ActiveSheet.Range("a2:a1005").Find("").Select
ActiveCell.Value = barcode
ActiveCell.Offset(0, 1).Value = Aname
ActiveCell.Offset(0, 2).Value = Email
Sheet1.Activate
ActiveSheet.Cells(2, 1) = ""
ActiveSheet.Cells(2, 1).Select
End Sub
Barcode-related videos are awesome! Thanks BH
Glad you like them!
the command should be put in active sheet or should do module..how to create that
No, I disagree, when you receive the product is when you notice the damage
You are the best, amazingly done. Is it possible to add several time slots where, the guest for example go for beeak time and return back?. Thanks in advance.
you should look at this video, it does something like what you want ruclips.net/video/17apUu-WvTA/видео.html
@@BarbHendersonconsulting Thanks again Barb, som of your affiliate links not working, like the one for office 2019.
Great video as always Barb, I've run into a slight problem when using this. I noticed if I entered the same ID number after it has gone through its cycle(In + Out with Total hours). And I re-enter the same ID number to create a new entry for a different date, when I clock out the ID it will create an entirely new entry in the below row but doesn't complete the existing one that's already there. Anything to remedy this?
Thanks, I did not design this for returns but if you use the return code from this video ruclips.net/video/jOeEPO8xwr8/видео.html it should work for you
Hi, I have a question after scanning a item I want the scanned barcode to be selected so that I can scan a 2nd item directly without having to delete it manually. Can you help me with that. What should I enter as code. Thanks ;)
The last line of the code should do that " ActiveSheet.Cells(2, 1) = ""
This is video No. 487 that I'm watching ❤️❤️
Thank you so much!
an interesting video. How can I entry data with the same ID, but a different date?
if the data is already there you can enter as many times as you want
How much would you charge to make a custom excel spreadsheet?
contact me at easyexcelanswers@gmail.com
I want to put the barcode in B3, and every time the barcode will be shown in a single column, like C 5.Voucher Number, C6 Product Name.C7 Entry Date( I have 2sheets..one home and secend. sheet of Barcode Number, Product Name, Voucher Number, Name Page, and Home page,)
Can you help me?
Sounds like you need some custom work. Contact me at easyexcelanswers@gmail.com