message box not appearing

[depcdivr]

I was working on some code with some help from you guys. I have the code running now but when I search for an item if its not there I want to display a message box announcing it. When I run the code normally it skips the msgbox code(or displays it someplace not visible to the user). IF I put a stop command in, the code will execute perfectly and display the msgbox as requested. If I run it normally it skips it again.

I am totally baffled by this one.

Private Sub barcode_Change()
    Dim Found           As Range
    Dim Search          As String
    Dim ws              As Worksheet
    Dim SearchColumn    As Variant
    
    Search = Me.Barcode.Value
    If Len(Search) <> 13 Then Exit Sub
    
    Set ws = ThisWorkbook.Worksheets("Physical Inventory List")
    
    SearchColumn = 5
    
    Set Found = ws.Columns(SearchColumn).Find(Search, LookIn:=xlValues, lookat:=xlWhole)
    If Not Found Is Nothing Then
        
        With Found

            .Offset(, 2).Value = Found.Offset(, 2) + Me.qty.Value
            Confirmation.Caption = Found.Offset(, -3) & " has been recorded in inventory"
            inv_count.Caption = "Counted " & Found.Offset(, 2) & "     Inventory " & Found.Offset(, 1)
        End With
        
      Else

'this is the msgbox that I want to display if the barcode is not found in the search.
        res = MsgBox("Barcode " & Search & " Not Found.  " & Chr(10) & "Try again", 48, "Not Found")
        
    End If
    
Me.Barcode.Value = ""
Me.Barcode.SetFocus
   
    
End Sub

 

[johnnyL]

Take off the 'res = ' & the parenthesis.

 

[McCITech]

Not sure what the , 48, is for, but it is causing the code to throw an error.

MsgBox "Barcode " & Search & " Not Found.  " & Chr(10) & "Try again", vbOKOnly

 

[Joe4]

Not sure what the , 48, is for, but it is causing the code to throw an error.

MsgBox "Barcode " & Search & " Not Found.  " & Chr(10) & "Try again", vbOKOnly

I am not sure if this is what is causing your issue, but you should NEVER use reserved words (words of existing functions, methods, properties, etc) as the names of your variables! "Search" is a reserved word. Try something else instead.

 

[johnnyL]

@McCITech You chopped off the title for the msgbox, and the vbOKOnly is not needed.

 

[depcdivr]

Tried all of these suggestions and still the same thing.

 

[Joe4]

Try placing a line break at this line of the code:

    Search = Me.Barcode.Value

Then, when your code is triggered, it should stop there, and you can progress through your code one line at a time using the F8 key.
That way you can see exactly which steps of the code it goes through, and you can hover any variable that you have passed to see that the current value is.

Often times, the issue will become readily apparent when you do that.

 

[depcdivr]

Try placing a line break at this line of the code:

    Search = Me.Barcode.Value

Then, when your code is triggered, it should stop there, and you can progress through your code one line at a time using the F8 key.
That way you can see exactly which steps of the code it goes through, and you can hover any variable that you have passed to see that the current value is.

Often times, the issue will become readily apparent when you do that.

Joe4,

I have done that. When I use F8 key to step through the program it works and the msg box appears. When I let it run it without the stop it does not appear. Hence my frustration

 

[Joe4]

Are you on the "Physical Inventory List" sheet when the code is run?

 

[depcdivr]

Are you on the "Physical Inventory List" sheet when the code is run?

yes. The userform pops up on top of the excel sheet and I can see the code updating things in the background. But whenever that msgbox is called the code just runs right past it unless I put a line stop in and step through it.