SOLVED

Excel Visual Basic help

Copper Contributor

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

23 Replies
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
best response confirmed by marvel2r (Copper Contributor)
Solution

@marvel2r 

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

@Hans Vogelaar Trying to get end product to look something like this.

 

Scan barcodeScan barcodeData sheet 1st scanData sheet 1st scanData sheet 2nd scanData sheet 2nd scan

ADP = barcode
My bad it works! So on sheet1 I just tie the macro to a button. Your awesome!

@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?

@marvel2r 

Do you really want two versions of the workbook, or one workbook where only some people can view the second sheet?

One workbook where only some can view second sheet

@marvel2r 

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:

S1434.png

Thank you so much!

@Hans Vogelaar  A quick edit, I added another sheet to hide then unhide with password. Will the one password unhide both?

@marvel2r 

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.

Im not sure how to add the third sheet to unhide with password like the other sheet.
p.s. Is it possible to duplicate the macro of the "Form" where one puts a number in a box which when entered logs it on the 2nd sheet with timestamp (like we have it) and another number in a different box on the Form sheet that just requires it to send the number to another cell within the same row?

@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.

 

edit.PNG

@marvel2r 

This would cause problems if you entered 12345 more than 2 times since there is room for only 2 timestamps...

is it possible 12345 : timestamp : _____ : ML10 then the second 12345 just finds row and inputs the second timestamp? Or is all based on offset relative to the 12345 cell?

@marvel2r 

That's possible, but what should happen if you enter 12345 a third time, intentionally or by accident?

Well I guess Im thinking employee bring defective equipment "ML 10" to a manger switch it out for another unit then manager goes in to the work book and manually edits the employee assigned unit, so the employee is only "scanning" in their number twice.
1 best response

Accepted Solutions
best response confirmed by marvel2r (Copper Contributor)
Solution

@marvel2r 

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

View solution in original post