Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Evening all

    I have a worksheet with a button that performs a web query to update rates, I would like to have the cells locked before calculation, then unlocked during calculation and re-locked after. I have done this before without any problem but for some reason it is not working this time, the code I am using is

    Code:
    Public Sub UnProtectMe()
    
    Dim oSht As Worksheet
    For Each oSht In Worksheets
    oSht.Unprotect "password"
    Next oSht
    
    ActiveWorkbook.RefreshAll
    
    For Each oSht In Worksheets
    oSht.Protect "password"
    Next oSht
    End Sub
    However I get the error shown below?
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Does your workbook contain one or more protected chart sheets?


  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Does your workbook contain one or more protected chart sheets?
    Hi Hans

    Yes, both are protected by the same password
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Change the declaration

    Dim oSht As Worksheet

    to

    Dim oSht As Object

    and change both occurrences of

    For Each oSht In Worksheets

    to

    For Each oSht In Sheets

    That way, the code will unprotect and protect both worksheets and chart sheets.


  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Change the declaration

    Dim oSht As Worksheet

    to

    Dim oSht As Object

    and change both occurrences of

    For Each oSht In Worksheets

    to

    For Each oSht In Sheets

    That way, the code will unprotect and protect both worksheets and chart sheets.
    Thanks very much Hans

    Now I realise what I had done it makes more sense
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Change the declaration

    Dim oSht As Worksheet

    to

    Dim oSht As Object

    and change both occurrences of

    For Each oSht In Worksheets

    to

    For Each oSht In Sheets

    That way, the code will unprotect and protect both worksheets and chart sheets.
    Hi Hans

    Does it matter if the rows are hidden (the cells are unlocked) because I am still getting the error after making the changes, there are only 2 sheets and no charts.

    Thanks
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It shouldn't matter whether rows are hidden or not, as far as I know.

    Are you certain that the sheets have really been unlocked?

  8. #8
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by stevehocking View Post
    Hi Hans

    Does it matter if the rows are hidden (the cells are unlocked) because I am still getting the error after making the changes, there are only 2 sheets and no charts.

    Thanks
    Any chance of your posting a sanitized copy of the problematic workbook?
    Regards
    Don

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    It shouldn't matter whether rows are hidden or not, as far as I know.

    Are you certain that the sheets have really been unlocked?
    Hi Hans

    In truth I am not sure as it runs so fast I can't tell. I have unlocked the sheet and pressed the button and I have locked the sheet and pressed the button but it gives the same error each time.
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'm afraid it's impossible to tell what's causing the error without seeing (a copy of) the workbook.


  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I suspect it is because there are some queries that have their BackgroundQuery set to true. Then the refreshall method is executed asynchronously, so the refresh isn't done before you go ahead and protect the sheets again.

    So add something like:

    Dim oSht As Worksheet
    Dim oQt as QueryTable
    For Each oSht In Worksheets
    oSht.Unprotect "password"
    For Each oQt in oSh.QUerytables
    oQt.BackgroundQuery=False
    Next
    Next oSht

    Of course now you need to catch the chart sheets...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Hans, Jan Karel and Don

    Thanks for your interest and assistance. There are no charts (at least not excel charts?) in the workbook. I have as suggested attached a stripped down verion to this post.

    The WB is currently unprotected but when you click the refresh rates button it will show the dialog and lock the WB, the password is password. I have unhidden all rows and columns for ease of viewing but when they are hidden the cells are not locked.
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Jan Karel's guess is correct - you have a web query whose BackgroundQuery property has been set to True. It should be set to False, by using the code suggested by Jan Karel. The refresh will then work without an error message.


  14. #14
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Jan Karel's guess is correct - you have a web query whose BackgroundQuery property has been set to True. It should be set to False, by using the code suggested by Jan Karel. The refresh will then work without an error message.
    Thanks for the quick response Hans

    Prior to posting the workbook I did actually try to implement the code as shown below, I did also try and change it to objects and sheets as you had advised earlier in the thread but that didn't make any difference
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What happens if you click the button in the attached version of the workbook?

    [attachment=86563:Automated-Tariff.xls]

    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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