add a message box if a match using a userform list box doesn't match

[still learning]

Hi
I have a user form that a user will enter a name into a text box
The userform also has three option buttons that the user will then click one
That name will be used to extract data from a table using advance filter
And the result will show in a list box in the same userform.
The range that the list box looks for data is AU2:BC2
If the filter does not show any results (because the name doesn’t match anything in the data source), I want to have a message box come up.
Here is the message box macro

Sub noresults ()
If Range("au2:BC2").Value = "" Then
      MsgBox "      NO RESULTS"
  End If
End Sub

Here is the macro for one of the option buttons (there are three)

Private Sub OptionButton1_Click()
 With TextBox1  'birthdate
        ActiveSheet.Range("wbirth").Value = .Text
     End With
 Me.Hide
 birthday
Unload Me
  End Sub

Here is the birthday macro (which is the advance filter code)

Sub birthday()
       ' Query
    Range("bdata").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "bcriteria"), CopyToRange:=Range("bextract"), Unique:=False
End Sub

I don’t know where to put the “noresults” message box
If the list box is empty because the query didn’t find a match I would like to let the user know that something is wrong.

Mike

 

[Akuini]

I'm assuming that Range("bdata") & Range("bextract") are in different sheets.
If AdvancedFilter returns nothing, the row below Range("bextract") must be empty, so you could check it like this:

Sub birthday()
       ' Query
    Range("bdata").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "bcriteria"), CopyToRange:=Range("bextract"), Unique:=False
    If WorksheetFunction.CountA(Range("bextract").Offset(1)) = 0 Then  'check if row below  Range("bextract") is empty
        MsgBox "It's empty"
    End If
End Sub

if that doesn't work, can you show us what do Range("bdata") & Range("bextract") refer to?

 

[still learning]

Hi Akuini,
That works, thank you
Range "bdata" is the where the names are listed
Range "bcriteria" is the range where the text box puts the name and the filter looks for a match in the list
Range "bextract" is where the filter puts the match.
All of this is in the same worksheet.
......A user will bring up the userform and enter a name.
.....3 option buttons will have the filter look at 3 different lists and return a match...only 1 option will be clicked...to the list box
....now, if a match can not be found, the message box will come up. before, the list box was just empty.


mike

 

[Akuini]

All of this is in the same worksheet.

In this case, the code I gave you probably could give incorrect result.
1. What are the addresses of Range("bdata") & Range("bextract")?
2. How do you populate the listbox?

 

[still learning]

No its not working.
Range “bextract” is actually the heading for the filter. The Range that will be empty is AU2
Your statement seems to work till I changed the range to AU2
Now, its not
The user calls up the userform and puts in a name. the results return a date (birthday)

"Bdata" is where the list is ...AA1: AB50>>this is where the match will be found. the name is in AA and there is a date in AB
"Bextract" is where the filter puts the results...AU2:AV3..("bextract" is the heading of AU2..Birthday and AV2..Date
The list box gets populated by the results of the filter..AU2>>which will be blank if the match can't be found

the filter asked where to look for the data (Range"bdata"), then what is the criteria >>( what name....Range "bcriteria>>), then where to extract it to (Range "bextract"...the results are put in AU2 and AV2.
The Range "bextract" is only the heading

I have the criteria range deleted when the usf box is called for

mike

 

[still learning]

I forgot to add that the results ( how the list box gets populated) will always be one cell>>>AV2
So if a match is not found, either AU2 or AV2 will be empty. I picked AU2 for the message box

wonder if i can send the sheet and the userform???

mike

 

[Akuini]

wonder if i can send the sheet and the userform???

Yeah, that's better.
You could upload a sample workbook (without sensitive data) to a file-sharing site like Dropbox.com or Google Drive, and then share the link here. Also, ensure that the link is accessible to anyone.
My suggestion is: when using Advance Filter, it's better to have data & the result in different sheets because if they are in the same sheet then the result could end up getting hidden/filtered as the data source.

 

[still learning]

It'll take a couple of days and I'll get back to you
thank you for your help and patience

mike