MSGBox for VBA Error

[Zutalores]

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:

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

 

[Joe4]

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):

'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

 

[Zutalores]

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!!

 

[Zutalores]

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!!

Worked Perfectly!

 

[Joe4]

You are welcome.
Glad I was able to help!