Protected Sheet - Macro To Unprotect, Refresh Sql Query & Re-protect Fails

  • Hello all
    I'm new so please be patient. I have searched for similar threads and not found a solution to my problem.


    I have a protected worksheet containing an SQL query.
    I have two cells on the worksheet unlocked so that users can enter fresh parameters to feed the query.


    Because I have to use MS Query to interrogate our SQL server (current IT decree), the query won't support parameters directly when the query cannot be represented graphically - so I have to manually edit the sql command text and then refresh the query - or at least that was why I wrote the code below


    The problem:

    • My macro won't unprotect the sheet before it calls the SQL.
    • it therefore won't update the sheet to detail the DSN, command text and number of parameters (latter should be 0) - info just used to verify query
    • it won't run the query / return the data as the sheet is still protected


    Comment:
    I have had to remove sheet protection in order to allow the sql to execute and return data.
    If protection is removed, the query does execute and return data (changes in the "parameter" cell contents do cause the query to be correctly modified)
    I could comment out the debug info but the query still doesn't return data if the sheet is protected.
    I apprecaite I have mixed code at present... e.g. still using both

  • Re: Protected Sheet - Macro To Unprotect, Refresh Sql Query & Re-protect Fails


    Welcome to the forum. However, please read the rules and use code tags for VBA. I have added them for you this time. Normally the post would be deleted.


    I am moving this to the SQL forum.

  • Re: Protected Sheet - Macro To Unprotect, Refresh Sql Query & Re-protect Fails


    Firstly, a point about coding style.


    Always include an 'OPTION EXPLICIT' statement at the top of your code modules. This forces you to declare all your variables before use and can stop a lot of potential errors caused by typing mistakes.


    Secondly try the protection without using a password first (ie uncomplicate things). I use this all the time in my code and it works without problem:



    Regards


    Rich

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!