Adding msgbox to code

[johnsonk]

Hi,

Can anyone help, i am trying to get a msgbox to popup when no id is found.
I have a sheet called Info and in column G i have some names and in column H I have some id numbers.
I have a userformwith two textboxes when i scan an id into textbox7 that then searches the sheet
Info then populates textbox8 with the name in column G opposite that then puts textbox8 value into column H on the active
sheet which all works fine. What i would like is for a msgbox to popup if no id is found in column H
on the Info sheet. I am all new to this so any help would be great. Here is what I have at the minute.
Hope this makes sense.

Private Sub TextBox7_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets("Info").Range("H" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets("Info").Cells(i, "H").Value = (Me.TextBox7) Or _
Sheets("Info").Cells(i, "H").Value = Val(Me.TextBox7) Then
Me.TextBox8 = Sheets("Info").Cells(i, "G").Value
End If
Next
End Sub

Private Sub TextBox8_Enter()
Application.DisplayAlerts = False
Dim LastRow As Long, ws As Worksheet
Set ws = ActiveSheet
LastRow = ws.Range("H" & Rows.Count).End(xlUp).Row + 1
ws.Range("H" & LastRow).Value = TextBox8.Text
TextBox7.Value = ""
TextBox8.Value = ""
Application.DisplayAlerts = True
Unload out2
out3.Show
End If
End Sub

Regards

 

[mikerickson]

Try

Private Sub TextBox7_Change()
    Dim i As Long, LastRow As Long

    LastRow = Sheets("Info").Range("H" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
        If Sheets("Info").Cells(i, "H").Value = (Me.TextBox7) Or _
            Sheets("Info").Cells(i, "H").Value = Val(Me.TextBox7) Then
            Me.TextBox8 = Sheets("Info").Cells(i, "G").Value
           
            Exit Sub 'add <<<<
           
        End If
    Next
   
    MsgBox "not found" ' add <<<<<<<<
End Sub

 

[johnsonk]

Hi Mikerickson,

Thank you for the quick reply I have tried the code and it does not work.

Regards

 

[mikerickson]

"does not work" is a bit vague. More details about how it isn't working would help.

Also, by putting this in the Change event, you have a potential problem of the MsgBox comming up every key press of the entry into the textbox.
It might be better in the AfterUpdate event.

 

[johnsonk]

Hi Mikerickson,

Sorry for the short reply, I have changed the change event to AfterUpdate and now it works thank you so much.

Regards

 

[johnsonk]

Hi,

when the message box pops up when it has not found a match and I press ok it jumps straight to Private Sub TextBox8_Enter() how do I make it clear textbox 7 & 8 and start from Private Sub TextBox7_AfterUpdate() again.

Private Sub TextBox7_AfterUpdate()
Dim i As Long, LastRow As Long
LastRow = Sheets("Info").Range("H" & Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Sheets("Info").Cells(i, "H").Value = (Me.TextBox7) Or _
Sheets("Info").Cells(i, "H").Value = Val(Me.TextBox7) Then
Me.TextBox8 = Sheets("Info").Cells(i, "G").Value
Exit Sub
End If
Next
MsgBox "not found"
End Sub

Private Sub TextBox8_Enter()
Application.DisplayAlerts = False
Dim LastRow As Long, ws As Worksheet
Set ws = ActiveSheet
LastRow = ws.Range("H" & Rows.Count).End(xlUp).Row + 1
ws.Range("H" & LastRow).Value = TextBox8.Text
TextBox7.Value = ""
TextBox8.Value = ""
Application.DisplayAlerts = True
Unload out2
out3.Show
End If
End Sub

Regards

 

[mikerickson]

Try

TextBox7.Text = vbNullString
TextBox8.Text = vbNullString
TextBox7.SetFocus

 

[johnsonk]

Hi,

It still jumps to Private Sub TextBox8_Enter() rather than starting from Private Sub TextBox7_AfterUpdate() again.

Regards

 

[mikerickson]

Of course it goes to the Enter event.
You don't want it going to the AfterUpdate. The AfterUpdate checks to see if the user entered value is on the sheet. But there isn't any user entry yet.
You want the focus to go to the TextBox, so the user can enter the value that will later be checked by the AfterUpdate event.
And, by going to the TextBox, the Enter event fires.

To put the focus where you want it, you might try putting the data checking code in the BeforeUpdate event and, after the MsgBox, clear the two textboxes and set Cancel = True. That way, the focus never leaves the TextBox so the Enter event isn't triggered.

 

[johnsonk]

Hi

Sorry for the late reply, I have done what you have suggested and it now works perfectly thank you so much.

Regards