Excel macro data refresh and protection

Question

In an Excel macro I need to unprotect a sheet, refresh the data and then protect the sheet. The following works:

Dim rs As Worksheet
For Each rs In Worksheets
    rs.Unprotect Password:="SomePassword"
Next rs
ActiveWorkbook.RefreshAll

This does not:

Dim rs As Worksheet
For Each rs In Worksheets
    rs.Unprotect Password:="SomePassword"
Next rs
ActiveWorkbook.RefreshAll
For Each rs In Worksheets
    rs.Protect Password:="SomePassword"
Next rs

Produces the following error:

The cell or chart you are trying to change is protected and therefore read-only.

I've tried MANY methods to delay the continuing of the macro until the refresh is finished but nothing has worked. I've looked at methods on this forum and found none that work. What will work?

score 0 · Answer 1 · answered Feb 24 '15 at 01:49

0

Try one of the following:

Add DoEvents after ActiveWorkbook.RefreshAll. Like this:

Dim rs As Worksheet
For Each rs In Worksheets
    rs.Unprotect Password:="SomePassword"
Next rs
ActiveWorkbook.RefreshAll: DoEvents
For Each rs In Worksheets
    rs.Protect Password:="SomePassword"
Next

Set UserInterfaceOnly argument to true when protecting the workbook. Something like:
```
rs.Protect Password:="SomePassword", UserInterfaceOnly:=True
```

answered Feb 24 '15 at 01:49

L42

19,427
11
44
68

Neither works. I'm connecting to an Excel spreasheet as an "Other data source" and running an sql query. I neglected to say that originally if that makes a difference. – Rich Feb 24 '15 at 18:58
@Rich That doesn't make a difference. I have a lot of worksheets which connects to databases and worksheets as well using ADO and in one, I also use protect and unprotect sub routines (private subs). I do not encounter the same issue. – L42 Feb 25 '15 at 00:57

score 0 · Answer 2 · answered Jan 23 '16 at 01:33

I get the same error message with the following sub, I have noticed that the error message refers to worksheets containing pivot tables:

Private Sub Refresh()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
    wks.Unprotect
Next wks
ThisWorkbook.RefreshAll: DoEvents
For Each wks In ThisWorkbook.Worksheets
    wks.Protect UserInterfaceOnly:=True
Next wks
End Sub

Excel macro data refresh and protection

2 Answers2