Excel and SharePoint - Error Trapping for Windows Security Dialog Box


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.

Thank you.
Who is Participating?
So the object would be empty if the user selects "cancel", right?

If  objMyList = vbNullString Then "your code"

Will that help you out?
RussellbrownAuthor Commented:
I ran the code and when I select "Cancel" I get a run-time error 1004.

Ok, what I will do is to have an error trapping routine to divert it to an area and safely exit the routine.
wouldn't you prefer to use integrated user authentication (the computer will use the logon credentials to log into sharepoint site)?
if so, just make sure sharepoint topsite is in local intranet security zone in IE and the user authentication  is set to "automatically logon with current username and password" (in Security Level , click on Custom...)
this way the chances to hit the dreaded "what's your username and password" will be reduced dramatically...
RussellbrownAuthor Commented:
Thank you for the pointer on user authentication. That is very helpful and will see how to take it onboard.

Is there a way to programactically switch it "on" via excel vba? It is not absolutely necessary for the project but I am always keen to try new way.  Additionally, the users can do with the convenience. Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.