Forum Discussion
Excel Visual Basic help
Hello. Was wondering if you could help me with a workbook I'm trying to create. The purpose is to track employees with numbers; on the first sheet (Form) someone will scan their number, it will then get stored on a second sheet with time stamp. The next time the number is scanned it finds the previous entry then adds another time stamp in the column next to the first.
Like this coding but I need the "barcode" entered on one sheet with the data on another
Sub inout()
Dim barcode As String
Dim rng As Range
Dim rownumber As Long
barcode = Worksheets("Sheet1").Cells(2, 2)
Set rng = Sheet1.Columns("a:a").Find(What:=barcode, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If rng Is Nothing Then
ActiveSheet.Columns("a:a").Find("").Select
ActiveCell.Value = barcode
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "m/d/yyyy h:mm AM/PM"
Worksheets("Sheet1").Cells(2, 2) = ""
Else
rownumber = rng.Row
Worksheets("Sheet1").Cells(rownumber, 1).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = Date & " " & Time
ActiveCell.NumberFormat = "m/d/yyyy h:mm AM/PM"
Worksheets("Sheet1").Cells(2, 2) = ""
End If
End Sub
Try
Sub inout() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim barcode As String Dim rng As Range Dim rownumber As Long Set ws1 = Worksheets("Form") ' *** Change Sheet2 to the name of the other sheet *** Set ws2 = Worksheets("Sheet2") barcode = ws1.Range("B2").Value Set rng = ws2.Range("A:A").Find(What:=barcode, LookIn:=xlValues, LookAt:=xlWhole) If rng Is Nothing Then Set rng = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(RowOffset:=1) rng.Value = barcode End If rownumber = rng.Row Set rng = ws2.Cells(rownumber, ws2.Columns.Count).End(xlToLeft).Offset(ColumnOffset:=1) rng.Value = Now rng.NumberFormat = "m/d/yyyy h:mm AM/PM" ws1.Range("B2").ClearContents End Sub
- The main problem is the active"" part I think. I don't know any other way.
- Or how can I edit this code so when a previously scanned number is scanned again it will find the row and add a timestamp one cell to the right of the first timestamp it did?
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("D16")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim w2 As Worksheet, nr As Long
With Application
.EnableEvents = False
.ScreenUpdating = False
Set w2 = Sheets("Sheet2")
With w2
nr = w2.Cells(w2.Rows.Count, "A").End(xlUp).Row + 1
If nr = 2 And w2.Range("A1") = vbEmpty Then nr = 1
w2.Range("A" & nr) = Target.Value
w2.Range("B" & nr) = Now()
w2.Range("B" & nr).NumberFormat = "mm/dd/yyyy hh:mm:ss AM/PM"
w2.Columns("A:B").AutoFit
End With
Target.Clear
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub Try
Sub inout() Dim ws1 As Worksheet Dim ws2 As Worksheet Dim barcode As String Dim rng As Range Dim rownumber As Long Set ws1 = Worksheets("Form") ' *** Change Sheet2 to the name of the other sheet *** Set ws2 = Worksheets("Sheet2") barcode = ws1.Range("B2").Value Set rng = ws2.Range("A:A").Find(What:=barcode, LookIn:=xlValues, LookAt:=xlWhole) If rng Is Nothing Then Set rng = ws2.Range("A" & ws2.Rows.Count).End(xlUp).Offset(RowOffset:=1) rng.Value = barcode End If rownumber = rng.Row Set rng = ws2.Cells(rownumber, ws2.Columns.Count).End(xlToLeft).Offset(ColumnOffset:=1) rng.Value = Now rng.NumberFormat = "m/d/yyyy h:mm AM/PM" ws1.Range("B2").ClearContents End SubHansVogelaar Trying to get end product to look something like this.
Scan barcodeData sheet 1st scanData sheet 2nd scan
- ADP = barcode
- My bad it works! So on sheet1 I just tie the macro to a button. Your awesome!
HansVogelaar Can I have two versions of this workbook? One where employees can only see/access sheet1 Form; and one where managers see/access both sheets?
Do you really want two versions of the workbook, or one workbook where only some people can view the second sheet?
