error line in a userform code

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
618
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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
152
Office Version
  1. 2010
Platform
  1. Windows
Hello!
VBA Code:
    If Range("BA3").Value = "" Then
        MsgBox "NO RESULTS"
    End If
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,779
Office Version
  1. 365
Platform
  1. Windows
....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)
 

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
618
Office Version
  1. 365
Platform
  1. Windows
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
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,779
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
618
Office Version
  1. 365
Platform
  1. Windows
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
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,779
Office Version
  1. 365
Platform
  1. Windows
Thanks Mike. So far, so good...
 

Watch MrExcel Video

Forum statistics

Threads
1,123,393
Messages
5,601,403
Members
414,449
Latest member
Pashtun

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
Top