+ Reply to Thread
Results 1 to 3 of 3

Running Query with Sheet Protection and Password Does Not Work

  1. #1
    Registered User
    Join Date
    01-12-2011
    Location
    Lumberton, Texas
    MS-Off Ver
    Excel 2003
    Posts
    2

    Running Query with Sheet Protection and Password Does Not Work

    I'm new to posting on forum as well as new to VBA so please forgive me.

    I have some code that protects a sheet so that nothing can be changed on the sheet but also allows grouping/ungrouping functionality to work (i.e. enable outlining). Also on this same sheet, I have a SAP Business Warehouse query embedded onto the sheet (i.e. Excel Add-In). I am able to refresh my query if the sheet is protected but no password is used when creating the protection. However, when I use a password with my sheet protection, I get the error message "Function is not available on a protected worksheet" when I am trying to refresh the query. (fyi to refresh this query, the user must click an icon on the SAB Business Warehouse add-in toolbar).

    Below is the code I am using to set the protection and the password.

    Please Login or Register  to view this content.
    I have tried without VB code to turn sheet protection on with and without a password and I am getting the same effect (i.e. query will refresh when not using a password but will not refresh when using a password).

    I prefer not to have VB code that the user has to use to refresh the query (i.e. turn off protection, run query, turn on protection). I guess if that is the only option, then I will need to do this. Any ideas as to how this can be done? Thks!

  2. #2
    Forum Contributor
    Join Date
    05-09-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    315

    Re: Running Query with Sheet Protection and Password Does Not Work

    I guess it depends how the add in is written. You could possibly amend it (assuming the add in isn't password protected) to include a line at the start which unprotects your sheet and a line at the end to re-protect it.

    Can you attach the add in to your post?

  3. #3
    Registered User
    Join Date
    01-12-2011
    Location
    Lumberton, Texas
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Running Query with Sheet Protection and Password Does Not Work

    Thanks for the quick reply.

    As far as the add-in, it is password protected and cannot be changed. Unfortunately, I cannot attach the add-in to this post as I do not have the right to distribute it in any manner.

    Do you have any ideas as to what might be able to be done with VBA code to enable this to work?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1