Ndtyjky

I am currently working on a project that has me working with an Excel "database".
It is going to be used to keep track of production of certain items and every now and then, human mistakes can happen and there have been cases of people scanning the same item more than once, which later on causes problems when it's time to pack it up and send to the client.

I managed to have an alert popup, to prevent people from scanning the same item more than once unless they're dealing with a rework.

My main issue right now, is to figure out how to change the value of a cell, whenever there is a dupe or that item is being reworked.

If the item is scanned and it's already present in the "database", there is a MsgBox with vbYesNo buttons. If people click "Yes", it's a rework. If people click "No", it's an error and they exit the sub.

I need to come up with a way to handle the rework and have it change the values of the cells in the same row as the original item.

Can anyone help me or point me in the right direction? I've read some similar questions on this topic here, but so far none had a clear answer.

Here's the code I have so far.

Private Sub gbatchd_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'Check DB for duplicate value



Set depo = dbgrids.Sheets("Deposition")

Set found = depo.Columns("A").Find(what:=valuetofind, LookIn:=xlValues, lookat:=xlWhole)



valuetofind = gbatchd.Text

FR = depo.Range("A" & Rows.Count).End(xlUp).Row



If KeyCode = 13 Then

    For i = 1 To FR

        If gbatch.Cells(i, 1).Value = valuetofind Then

            MsgBox "This batch has already been deposited!" & vbCrLf & "Rework?", vbYesNo, "Rework?"

            If answer = vbNo Then

                Exit Sub

            Else

                depo.Cells(found.Row, 5).Value = "Rework"

            End If

        End If

    Next

End If

End Sub

Setting aside any criticism of the solution's architecture, what your code is missing is the assignment to the answer variable:

answer = MsgBox("This batch has already been deposited!" & vbCrLf & "Rework?", vbYesNo, "Rework?")

Use Option Explicit and declare your variables with the appropriate types (check out this article).

A cleaned-up version of your code might look something like this:

Option Explicit 'At the very top of your module.



'... Other code ...



Private Sub gbatchd_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 'Check DB for duplicate value

    Dim wsDepo As Excel.Worksheet

    Dim rngFound As Excel.Range

    Dim sValueToFind As String

    Dim answer As VbMsgBoxResult



    If KeyCode = KeyCodeConstants.vbKeyReturn Then

        'Barcode reader has sent the Enter (Return) key.

        'Attempt to find the value.

        sValueToFind = Trim(gbatchd.Text)

        Set wsDepo = dbgrids.Worksheets("Deposition")

        Set rngFound = wsDepo.Columns(1).Find(What:=sValueToFind, LookIn:=xlValues, LookAt:=xlWhole)



        If Not rngFound Is Nothing Then

            'Value was found. Ask whether it is a rework.

            answer = MsgBox("This batch has already been deposited!" & vbCrLf & "Rework?", vbYesNo, "Rework?")

            If answer = VbMsgBoxResult.vbYes Then

                wsDepo.Cells(rngFound.Row, 5).Value = "Rework"

            End If

        End If



        'Cleanup.

        Set rngFound = Nothing

        Set wsDepo = Nothing

    End If

End Sub