Excel and SharePoint - Error Trapping for Windows Security Dialog Box
Posted on 2012-09-14
I have a vba routine in MS Excel to download SharePoint List to a spreadsheet in the local drive. So when the code executes the following line:
Set objMyList = objSheet.ListObjects.Add(xlSrcExternal, _
Array(strSPServer, listname, VIEWNAME), False, , Range("A1"))
a dialog box will pop up requesting for the userid and password plus 2 buttons: "OK" "Cancel". The title to the dialog box is: Windows Security and narration is: Enter your credentials
I would like to introduce an "error trapping" routine so that when the user selects "Cancel" , the code will take it to an area where I could insert the appropriate statements to ensure consistency in the flow of the program logic.
How can I write this in vba code? Is there an equivalent of the constants / values for "OK" / "Cancel" similar to VBA Msgbox Function e.g. Constant: vbYesNoCancel with values assigned to the selection which could then be used in subsequent If / Select Case statements.