MSGBox for VBA Error

Zutalores

New Member
Joined
Oct 9, 2023
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello. I have a file that refreshes severl queries to pull in data from external sources. Sometimes there is no data to bring in and the macro errors out. I would like to have a message box pop up notifiying the user that there is no data rather than the defualt macro runtime error. Any help would be greatly appreciated.

Below is the error I receive:
1701370378685.png

1701370398694.png




Below is the current VBA Code:

Sub MBORefresh_RefreshAllData()
'
' MBORefresh_RefreshAllData Macro
'

'
Dim a As Range
Dim b As Range
Dim c As Range
Dim FirstRow As Long
Dim SecondRow As Long
Dim LastRow As Long


FirstRow = Range("A" & Rows.Count).End(xlUp).Row
For Each a In Range("A2")
If a.Value = 0 Then
MsgBox "Missing CGroup or Sold-To Information"
Exit Sub
End If
Next a


SecondRow = Range("D2")
For Each b In Range("D2")
If b.Value = 0 Then
MsgBox "Missing Start Date Information"
Exit Sub
End If
Next b

LastRow = Range("D3")
For Each c In Range("D3")
If c.Value = 0 Then
MsgBox "Missing End Date Information"
Exit Sub
End If
Next c


answer1 = MsgBox("Depending on User Input, this may take 10 minutes or more to complete. Please remain patient as Excel may freeze during this process. Please click Yes to Continue or No to Cancel.", vbQusetion + vbYesNo + vbDefaultButton2, "ATTENTION")

If answer1 = vbYes Then


With ThisWorkbook

For Each objConnection In .Connections

'Get current background-refresh value

bBackground = objConnection.OLEDBConnection.BackgroundQuery

'Temporarily disable background-refresh

objConnection.OLEDBConnection.BackgroundQuery = False

'Refresh this connection

objConnection.Refresh

'Set background-refresh value back to original value

objConnection.OLEDBConnection.BackgroundQuery = bBackground

Next

'Save the updated Data

.Save

End With



answer1 = MsgBox("Data Capture Complete. Results can be reviewed on the MBO Refresh tab.", vbOKOnly, "You're All Set")
Else: answer1 = MsgBox("Data Capture Cancelled!", vbOKOnly, "Attention")


End
End If
End Sub
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Welcome to the Board!

You can add Error Handling to handle that. Replace everything from that yellow line on down with the following code (I highlighted the new rows in red):
Rich (BB code):
'Refresh this connection

On Error GoTo err_chk
objConnection.Refresh
On Error GoTo 0

'Set background-refresh value back to original value

objConnection.OLEDBConnection.BackgroundQuery = bBackground

Next

'Save the updated Data

.Save

End With



answer1 = MsgBox("Data Capture Complete. Results can be reviewed on the MBO Refresh tab.", vbOKOnly, "You're All Set")
Else: answer1 = MsgBox("Data Capture Cancelled!", vbOKOnly, "Attention")


End
End If

Exit Sub

err_chk:
    If Err.Number = 1004 Then
        MsgBox "There is no data!"
    Else
        MsgBox Err.Number & ":" & Err.Description
    End If

End Sub
If you do a Google search on "Excel VBA Error Handling", you will find lots of good tutorials on how to do this.

One last thing, when posting VBA code here, please use the Code Tags, which maintains formatting and makes the code much easier to read and work with. It is really easy to do (click one button). See here: How to Post Your VBA Code
 
Upvote 0
Solution
Thanks so much! I will give it a try. I had searched all over Google and found lots of info. I just couldn't figure out where to put everything. Thanks Again!!
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,183
Members
452,615
Latest member
bogeys2birdies

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