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

Stacy Rueda

Board Regular
Joined
Jun 23, 2016
Messages
87
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,054
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Hi there. Adding a couple of lines (in red) will do the trick for you.

Code:
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

Board Regular
Joined
Jun 23, 2016
Messages
87
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

Board Regular
Joined
Jun 23, 2016
Messages
87
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

Well-known Member
Joined
Oct 5, 2015
Messages
1,054
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,955
Messages
5,599,040
Members
414,280
Latest member
morralletti

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top