VBA excel Userform display warning message if no data in target sheet

[Stacy Rueda]

Hi guys,

I used VBA excel userform that will display details when I enter Job number from the excel SUMMARY sheet. It works perfectly but my problem is I want it to display message if job number does not exist from the SUMMARY sheet. How can I insert message error inside this code. Please see below. thank you in advance



Private Sub TextBox1_Change()
Dim i As Long, lastRrow As Long
lastrow = Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
If Sheets("SUMMARY").Cells(i, "A").Value = (Me.TextBox1) Or _
Sheets("SUMMARY").Cells(i, "A").Value = Val(Me.TextBox1) Then
Me.TextBox2 = Sheets("SUMMARY").Cells(i, "B").Value
Me.TextBox3 = Sheets("SUMMARY").Cells(i, "C").Value
Me.TextBox4 = Sheets("SUMMARY").Cells(i, "D").Value
Me.TextBox5 = Sheets("SUMMARY").Cells(i, "E").Value
Me.TextBox6 = Sheets("SUMMARY").Cells(i, "F").Value
Me.TextBox7 = Sheets("SUMMARY").Cells(i, "G").Value
Me.TextBox8 = Sheets("SUMMARY").Cells(i, "H").Value
Me.TextBox9 = Sheets("SUMMARY").Cells(i, "I").Value
Me.TextBox10 = Sheets("SUMMARY").Cells(i, "J").Value
End If
Next
End Sub

 

[jmacleary]

Hi there. Adding a couple of lines (in red) will do the trick for you.

Private Sub TextBox1_Change()
Dim i As Long, lastRrow As Long
lastrow = Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lastrow
If Sheets("SUMMARY").Cells(i, "A").Value = (Me.TextBox1) Or _
Sheets("SUMMARY").Cells(i, "A").Value = Val(Me.TextBox1) Then
Me.TextBox2 = Sheets("SUMMARY").Cells(i, "B").Value
Me.TextBox3 = Sheets("SUMMARY").Cells(i, "C").Value
Me.TextBox4 = Sheets("SUMMARY").Cells(i, "D").Value
Me.TextBox5 = Sheets("SUMMARY").Cells(i, "E").Value
Me.TextBox6 = Sheets("SUMMARY").Cells(i, "F").Value
Me.TextBox7 = Sheets("SUMMARY").Cells(i, "G").Value
Me.TextBox8 = Sheets("SUMMARY").Cells(i, "H").Value
Me.TextBox9 = Sheets("SUMMARY").Cells(i, "I").Value
Me.TextBox10 = Sheets("SUMMARY").Cells(i, "J").Value
[COLOR=#ff0000]Exit Sub[/COLOR]
End If
Next
[COLOR=#ff0000]MsgBox "whatever error message you want"[/COLOR]
End Sub

 

[Stacy Rueda]

Hi @jmacleary, Thank you for the reply but the warning/error message appears already even when I type single digit only, Job number fields require 6 digits. I have to close every single moment the error message "no data found" eveytime i type number. is there any way to solve the above problem? Thank you in advance

 

[Stacy Rueda]

Hi @jmacleary, Thank you for the reply but the warning/error message appears already even when I type single digit only, Job number fields require 6 digits. I have to close every single moment the error message "no data found" eveytime i type number. is there any way to solve the above problem? Thank you in advance

Update: After checking, If I type letters and 0 characters, warning/error message not appear, but I need to input numbers in Job numbers sometimes with letters at the end of 6 digits.

 

[jmacleary]

OK. If you have a command button that is clicked, you could move the code into the command buttons click routine, or create a simple button and put the code in the click routine. You then type into your textbox, and click the button when done.