error line in a userform code

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
774
Office Version
  1. 365
Platform
  1. Windows
Hi

I have a userform that after data is entered, an advance filter finds the results.
Is there a way to have a message box come up if no results are found?
If there are no results, BA3 (which is the start of the Extract range) will be empty
I tried using
On Error Go To errorline
Errorline: msgbox “ NO RESULTS”
But the msgbox comes up if BA3 has data or is empty
He’s the userform macro shortened for clarity
VBA Code:
 Private Sub CommandButton1_Click()
        With TextBox1 
        ActiveSheet.Range("af3").Value = .Text
     End With
Me.Hide
End With
chsavfind
Unload Me"
End Sub
Chsavfind is the advance filter macro


mike
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hello!
VBA Code:
    If Range("BA3").Value = "" Then
        MsgBox "NO RESULTS"
    End If
 
Upvote 0
....and if you're happy with LazyBug's solution, you may wish to stop running the rest of the routine if BA3 IS empty - in order to prevent errors occurring later in the code, thus:

Code:
If Range("BA3").Value = "" Then MsgBox "NO RESULTS" : Exit Sub
(no real need for the "End If" if you're only running one or two commands after the statement returns TRUE)
 
Upvote 0
Thank you LazyBug and sykes,
both worked great.
I'm putting it at the end of the code, so I can use either.
Wonder if there is a way to change the font in a msgbox?

mike
 
Upvote 0
Mike
Glad it's helped.

Changing the font in a message box is a different question, so should be posed in a separate thread, really. However....

As you're already coding & using userforms etc, I'd be inclined to use a new userform for your messages, and plop a label on it.
You could then change all of the properties of the label to suit (inc font, colour etc); use "label.caption" to display your msgs (or have them removed).
Another benefit of doing it this way, is that you have much more control over things; you could even make the userform modeless, and so keep your "messagebox" displayed, even after the user's acknowledged it (or not, if you don't wish them to have to keep doing this).
Over the years, I've found that all-but the occasional Windows msgbox becomes tiresome for the users, and they just get fed up with boxes with little messages popping up every 5 minutes (with some tiresome Windows noise), to which they're forced to react.
 
Upvote 0
I understand both points
I like the userform idea instead of a msgbox. the more i make custom stuff, the more i learn
yep, i should have posted anew.
thanks
hope your safe from he "virus"

mike
 
Upvote 0
Thanks Mike. So far, so good...
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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