Error Msg Coding Issue

[Craw]

Greetings,
I have a spreadsheet linked to a SharePointe list. I created a marco that is attached to a command button so that when it is activated it will refresh the data between the Excel spreadsheet and the SharePoint list. However if the user is not connected to the internet or the SharePoint site is down I would like an error msg box to pop-up with the following text in the following format;

Connectivity Issue due to:
- The SharePointe is Currently Down or;
- You are not connected to the Internet or;
- You are using an outside interner connection without VPN

Potential Solutions;
- Please try again in 5 minutes
- If you are using an outside internet connection ensure you are logged into to VPN
- Contact Help Desk at Ext 2555

Please note that I have tried the code shown below but it would not show the error msg. It would just ask me to debug the code or end the function.

Here is the code I have so far;

Sub Refresh()
'
' Refresh Macro
ActiveWorkbook.RefreshAll
On Error GoTo ErrMsg:
ActiveSheet.ListObjects("Table_owssvr_1").TableStyle = "Table Style 1"
Range("B:B,I:I").Select
Range("I1").Activate
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
Range("A3").Select
End With
 
Exit Sub
ErrMsg:
MsgBox ("Error Message will go here"), , "PLEASE READ"
End Sub

 

[laughingdevil]

I just copied and pasted that into excel 2007 and it worked fine.

Which version of Excel are you using?

 

[Craw]

I am using 2010 but for some reason if the connection is broken between my comp and the internet it doesn't prompt the error code.? Instead I get run-time error 1004...Any ideas?

 

[Hermanito]

Maybe you need to swap these lines:

ActiveWorkbook.RefreshAll
On Error GoTo ErrMsg:

Also, the colon is not needed there, better leave it out:

On Error GoTo ErrMsg
ActiveWorkbook.RefreshAll

In your code, if the RefreshAll caused an error, it would not be trapped.

 

[Craw]

Brilliant swaping the code lines did the trick thank you very much for your help.

Cheers to beers,

Craw

 

[Hermanito]

you're welcome, thanks for the feedback!