Excel and SharePoint - Error Trapping for Windows Security Dialog Box

Posted on 2012-09-14
Last Modified: 2012-09-26

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.
Question by:Russellbrown
    LVL 17

    Accepted Solution

    So the object would be empty if the user selects "cancel", right?

    If  objMyList = vbNullString Then "your code"

    Will that help you out?

    Author Comment

    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.
    LVL 6

    Expert Comment

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

    Author Comment

    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.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now