Unhide, refresh, hide problem

Topic

New Reply

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?

Reply | Quote

Replies

Does your workbook contain one or more protected chart sheets?

Reply | Quote

Does your workbook contain one or more protected chart sheets?

Hi Hans

Yes, both are protected by the same password

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

I’m afraid it’s impossible to tell what’s causing the error without seeing (a copy of) the workbook.

Reply | Quote

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?

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

What happens if you click the button in the attached version of the workbook?

Reply | Quote

What happens if you click the button in the attached version of the workbook?

Hi Hans

Thank you for that operfect solution, I could not understand why I could not get Jan Karel’s example to work and it was only when I copied and pasted your macro in did it work. Looking at it closely I think it had to do with the fact that in Jan Karel’s example it said

Next
Next 0Sht

but yours said

Next oQt
Next oSht

Is that assuption right?

Thanks to all for your help and interest

Reply | Quote

No, there was one typo in Jan Karel’s code: he used oSh instead of oSht in one place, but I had assumed that you would have spotted and corrected that.

Reply | Quote

No, there was one typo in Jan Karel’s code: he used oSh instead of oSht in one place, but I had assumed that you would have spotted and corrected that.

Hence the wisdom of ALWAYS using Option Explicit

Reply | Quote

Hence the wisdom of ALWAYS using Option Explicit

Amen!

Reply | Quote

No, there was one typo in Jan Karel’s code: he used oSh instead of oSht in one place, but I had assumed that you would have spotted and corrected that.

Sorry ’bout that, I didn’t test the code, just typed it in the reply window.

Reply | Quote