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.
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
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:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,643
Office Version
365
Platform
Windows
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:

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
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:

Forum statistics

Threads
1,081,708
Messages
5,360,777
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top