Userform - If value is not found then displays a MsgBox

maedenegro

New Member
Joined
Oct 7, 2019
Messages
2
Hi - I was hoping you could help me solve the following.
With the following code I can get a list of values from a Table and displayed on an UserForm; however, I would like to have a msgbox when the value is not found.

-----------------------------------------------------------------------------------

Private Sub CommandButton1_Click()
Dim x As Long
Dim y As Long
x = Sheets("ACCOUNT_ASSIGN").Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheets("ACCOUNT_ASSIGN").Cells(y, 1).Text = TextBox1.Value Then
TextBox2.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 2)
TextBox3.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 3)
TextBox4.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 4)
TextBox5.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 5)
TextBox6.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 6)
TextBox7.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 7)
End If
Next y
End Sub

--------------------------------------------------------------------------------------------

Thank you in advance.

Josue.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hi and welcome to MrExcel!
Just insert this code line after the Next y

Rich (BB code):
If y > x Then MsgBox "Not found"

And add Exit Sub before End If
 
Last edited:
Upvote 0
which value should be in the message box ????

perhaps one of the two message boxes :confused:

Code:
Private Sub CommandButton1_Click()
    Dim x As Long
    Dim y As Long
    x = Sheets("ACCOUNT_ASSIGN").Range("A" & Rows.Count).End(xlUp).Row
    For y = 2 To x
    If Sheets("ACCOUNT_ASSIGN").Cells(y, 1).Text = TextBox1.Value Then
        TextBox2.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 2)
        TextBox3.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 3)
        TextBox4.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 4)
        TextBox5.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 5)
        TextBox6.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 6)
        TextBox7.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 7)
    Else
        MsgBox Sheets("ACCOUNT_ASSIGN").Cells(y, 1).Text & vbCr & "Not found"
        MsgBox TextBox1.Value & vbCr & "Not found"
    End If
Next y
End Sub
 
Last edited:
Upvote 0
One more way

Rich (BB code):
Private Sub CommandButton1_Click()
  Dim x As Long
  Dim y As Long
  Dim IsFound As Boolean
  x = Sheets("ACCOUNT_ASSIGN").Range("A" & Rows.Count).End(xlUp).Row
  For y = 2 To x
    If Sheets("ACCOUNT_ASSIGN").Cells(y, 1).Text = TextBox1.Value Then
      TextBox2.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 2)
      TextBox3.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 3)
      TextBox4.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 4)
      TextBox5.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 5)
      TextBox6.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 6)
      TextBox7.Text = Sheets("ACCOUNT_ASSIGN").Cells(y, 7)
      IsFound = True
    End If
  Next y
  If Not IsFound Then MsgBox "Not found"
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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
Back
Top