VBA to Protect/Unprotect Worksheet and Workbook Excel 2016

[MM1963]

I have a workbook with many sheets. All the sheets and the workbook are protected. I'm using Excel 2016
Two of the sheets contain data from connected Access tables that needs to be refreshed periodically. However, when I click “refresh all” I get an error that the sheet is protected. After I unprotect the sheets and click “refresh all” I get an error message that the workbook is protected. I understand why because the range (and range name) where the Access table refreshes can change if the record count changes. The protected workbook prohibits this.
I need code that I can attach to a button that will:
• Use my password to unprotect worksheet1
• Use my password to unprotect worksheet2
• Use my password to unprotect the workbook
• Refresh All
• Use my password to protect worksheet1
• Use my password to protect worksheet2
• Use my password to protect the workbook
Any help would be appreciated.

 

[SkipVought]

Hi,

Turn on your macro recorder and record doing the steps that you outlined.

Assign the button to run that macro.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

There is VBA dedicated forum707.
Both workbook and worksheet objects have Protect methods with optional arguments, check object browser for details. To unprotect, use Unprotect method with password.
For worksheet you do not need to unprotct worksheets, protect again each worksheet by code with parameter UserInterfaceOnly:=True. This allows to change protected sheet by code (this feature is not stored, so you need to set it after opening the workbook).
UpdateLink method for workbook without parameter refreshes all links.

combo