Message box to appear on Userform if search item not found- Userform Macro

usmannajam · Jun 2nd 2016

Hi
I have a code which I am trying to work with to show message box pop up on userform when search function does not return item from the sheet2 of the data. Below is a workable code that do the search and put the items in respective text boxes; When does not found any item it doesn't return anything and I would need to show pop up message that " Item not found".

Code

Private Sub CommandButton1_Click()
row_number = 0
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("Sheet2").Range("B" & row_number)
    If item_in_review = TextBox1.Text Then
    TextBox2.Text = Sheets("Sheet2").Range("A" & row_number)
    TextBox3.Text = Sheets("Sheet2").Range("B" & row_number)
    TextBox4.Text = Sheets("Sheet2").Range("D" & row_number)
    TextBox5.Text = Sheets("Sheet2").Range("E" & row_number)
    TextBox6.Text = Sheets("Sheet2").Range("C" & row_number)


      
    Loop Until item_in_review = ""
End Sub

Display More

Not sure what to do to show message box when I item not found in the data set.
Prompt help will be highly appreciated.
Thanks

cytop · Jun 2nd 2016

Re: Message box to appear on Userform if search item not found- Userform Macro

You could simply check if Textbox2 is blank after the search... of course you need to choose a column which is guaranteed to have information in it. You could also declare a Boolean variable and set it True if the item is found. At the end you check if it is still False (It has a default value of False when declared) then show the message box.

Finally you could do it the 'proper' way - Use Worksheet functions to find the data and proceed accordingly.

Code

Private Sub CommandButton1_Click() 


     Dim r as Excel.Range


    Set r = Sheets("Sheet2").Range("B:B").Find(What:=Textbox1.Text, LookAt:=xlWhole, MatchCase:=False)


    if r is Nothing Then
        MsgBox "Not found...", vbExclamation
    Else
        TextBox2.Text = Sheets("Sheet2").Range("A" & r.Row).Value
        TextBox3.Text = Sheets("Sheet2").Range("B" & r.Row).Value
        TextBox4.Text = Sheets("Sheet2").Range("D" & r.Row).Value
        TextBox5.Text = Sheets("Sheet2").Range("E" & r.Row).Value
        TextBox6.Text = Sheets("Sheet2").Range("C" & r.Row).Value) 
    End If     
 
End Sub

Display More

PS: Your post on the discussion board has been deleted to keep things tidy.

usmannajam · Jun 2nd 2016

Re: Message box to appear on Userform if search item not found- Userform Macro

awesome...working smooth many thanks cytop

Quick question, can this code be alter to look for matching record as you type in the search field? e.g. if I am looking for Department ID, and as I am typing "Dep" and it start showing me matching options for Department Code, Department Person, etc.? Greatly appreciate your help here.

cytop · Jun 2nd 2016

Re: Message box to appear on Userform if search item not found- Userform Macro

Yes, but I wouldn't have done it that way... One of the tricks of posting in forums is get your question sorted before posting and include all the relevant information. Tacking on another request later sometimes affects any solutions already posted.

usmannajam · Jun 2nd 2016

Re: Message box to appear on Userform if search item not found- Userform Macro

Great. Thanks for the advice. I will do accordingly.

Message box to appear on Userform if search item not found- Userform Macro

Participate now!

Share

Users Viewing This Thread