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

 

[Joe4]

Can you post your current code, after you have made all the revisions, as per the discussion in this thread, so I can make sure I am testing the EXACT same code that you are?

 

[depcdivr]

Here it is

Dim ws As Worksheet


Private Sub barcode_Change()
    Dim Found           As Range
    Dim Barcode          As String
    Dim ws              As Worksheet
    Dim SearchColumn    As Variant
    
    Barcode = Me.Barcode.Value
    If Len(Barcode) <> 13 Then Exit Sub
    
    Set ws = ThisWorkbook.Worksheets("Physical Inventory List")
    
    SearchColumn = 5
    
    Set Found = ws.Columns(SearchColumn).Find(Barcode, 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


        MsgBox "Barcode " & Barcode & " Not Found.  " & Chr(10) & "Try again", vbOKOnly
        
    End If
    
Me.Barcode.Value = ""
Me.Barcode.SetFocus
   
    
End Sub


Private Sub UserForm_Click()

End Sub

 

[depcdivr]

If I put the break on the "else" command the it works. See the attached picture. If that break is not there then it just moves on

 

[Joe4]

I don't think it is the cause of the issue, but why do you declare "ws" twice?
Once outside any procedures at the top, and once inside a procedure?
Is that just a typo, or do you really have that?

Any global variable should only be declared once, outside of any procedure.
It should not be declared a second time within a procedure.
It could confuse Excel, as it, may not be able to determine if it is a global variable or not.

Also, how is the column with the barcodes on the sheet formatted?

 

[McCITech]

Just a thought, but as this is a userform, is it being called vbmodeless by chance?

 

[depcdivr]

I don't think it is the cause of the issue, but why do you declare "ws" twice?
Once outside any procedures at the top, and once inside a procedure?
Is that just a typo, or do you really have that?

Any global variable should only be declared once, outside of any procedure.
It should not be declared a second time within a procedure.
It could confuse Excel, as it, may not be able to determine if it is a global variable or not.

Also, how is the column with the barcodes on the sheet formatted?

That was a typo. I was cutting and pasting code from other programs that I have done and must have hit that declaration line twice.

the columns for barcode are numbers but are formatted as text. I can successfully scan barcodes that are there and it works just fine. When I scan a barcode that is not in the list I want that msgbox to pop up. I would even take that msgbox if it can't find the barcode because it is formatted incorrectly.

 

[depcdivr]

Just a thought, but as this is a userform, is it being called vbmodeless by chance?

I am not sure. how can you tell?

 

[McCITech]

I am not sure. how can you tell?

Where ever you have the code to call the userform

Userform1.show

Does it have anything after the .show?

 

[depcdivr]

I am not sure. how can you tell?

The showmodal was set to true. I just set it to false and got the same results.

 

[depcdivr]

Where ever you have the code to call the userform

Userform1.show

Does it have anything after the .show?

Right now I am only running the code my pressing play in the editor. I am not calling the code from any other location at the moment.