May 13 2022 11:24 PM
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
May 13 2022 11:26 PM
May 14 2022 12:37 AM
May 14 2022 02:03 AM
SolutionTry
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
May 14 2022 02:10 AM
May 14 2022 02:20 AM
May 14 2022 02:49 AM
@Hans Vogelaar 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?
May 14 2022 02:53 AM
Do you really want two versions of the workbook, or one workbook where only some people can view the second sheet?
May 14 2022 02:55 AM
May 14 2022 03:17 AM
In the Visual Basic Editor, double-click ThisWorkbook, then copy the following code into it:
Private Sub Workbook_Open()
Worksheets("Form").Activate
Worksheets("Sheet2").Visible = xlSheetVeryHidden
End Sub
Change Sheet2 to the name of the other sheet.
This hides the second sheet, while it can only be unhidden using VBA,not from the Excel interface.
In the same module that contains the inout macro, create a new macro:
Sub UnhideSheet()
If InputBox("Enter password", "Activate other sheet") = "mypassword" Then
With Worksheets("Sheet2")
.Visible = xlSheetVisible
.Activate
End With
Else
MsgBox "Incorrect password. Access denied.", vbCritical
End If
End Sub
Change mypassword to the password you want to use (it should be more secure than mypassword!), and change Sheet2 to the name of the other sheet.
Switch back to Excel.
Create a new button on the Form sheet, and assign the UnhideSheet macro to it. Set its caption to "Show other sheet" or something like that.
Tell the managers who will need to access the second sheet the password,
Warning: VBA-savvy users will be able to view the code and thus the password.
If you would like to prevent that, you have to lock the VBA project for viewing:
May 16 2022 10:12 PM
@Hans Vogelaar A quick edit, I added another sheet to hide then unhide with password. Will the one password unhide both?
May 17 2022 12:57 AM
That's up to you. If all persons who will be allowed to unhide one of the sheets will also be allowed to unhide the other one, you can use one macro, with one password.
But if employee A should only be allowed to unhide sheet A and employee B to unhide sheet B, use two different macros, with two different passwords.
May 17 2022 01:32 AM
May 17 2022 01:45 AM
@Hans Vogelaar So on the Form sheet one would enter 12345 in a cell then in another type ML 10; press the "Enter" macro button; values are logged in the 2nd sheet "Data" and the Form cell clear back to blank. Then when they do it a second time it searches for the previous row to logged data, the ML 10 value should end up in cell next to each other.
May 17 2022 03:05 AM
This would cause problems if you entered 12345 more than 2 times since there is room for only 2 timestamps...
May 17 2022 03:11 AM
May 17 2022 03:14 AM
That's possible, but what should happen if you enter 12345 a third time, intentionally or by accident?
May 17 2022 03:19 AM
May 14 2022 02:03 AM
SolutionTry
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