Macros with protected sheets

[momentumons]

Hello! I have a model that has two macros in it that both basically copy and paste data. How can I get these to run but also protect the sheets so that no one accidentally makes changes to these (they should be making changes in the master sheets and using the macros to copy the data)...

My FIRST MACROS IS:

Sub REFRESH_DATA_TAB()
'
' REFRESH_DATA_TAB Macro
' ctrl alt f5 - to run the power query
'

'
ActiveWorkbook.RefreshAll
End Sub

(It activates a power query from another workbook)

MY SECOND MACRO IS:

Sub ARCHIVE_FCST()
'
' ARCHIVE_FCST Macro
'

'
End Sub

(It's a series of recorded steps)


Thanks!

 

[CephasOz]

No worries. It looks like you've pasted the code I gave you into a normal module. To start, delete that.

Then, the code that I gave you for the Workbook_Open event needs to be added to ThisWorkbook. Use Alt-F11 to go into the VBA editor, find ThisWorkbook for your workbook in the Project Explorer pane on the left, and double-click it. Then, in the code area on the right, paste the code that I gave you. Save the workbook, close it, and re-open it. (The code only gets run when the workbook is opened.) Then any of your macros should be able to make the changes that you want even on protected worksheets.

 

[momentumons]

Thanks @CephasOz ! I really appreciate your help. This sounds like a fab solution... but I am clearly doing something wrong....

If I run my 'refresh' macro when the sheet is not protected it works. But if I run it with a protected macro it does not refresh the data. Since adding your code, I no longer get any errors that say it can't be executed because the sheet is protected...but the data doesn't update... Any ideas?

 

[momentumons]

@CephasOz it is also now asking for my password to unprotect the sheet when i open it up... ;/

 

[momentumons]

@CephasOz - sorry for the drip feed of info.... the 'refresh' macro is being recognized as it go to the correct tab in the book, but it doesn't update the data from the power query...

 

[momentumons]

I found this: Can no longer refresh data in a Protected Workbook since Excel version 1803

"refresh of Power Query queries works if you protect the workbook structure. It will not work if you protect the worksheet...
"Is there any way to override this? I want to protect most of a sheet that has a query in it, but allow users to edit certain cells."
"I am not aware of such a workaround. However, I suggest you publish your question at the General Discussion forum. Maybe someone will have a creative solution for you."


Hopefully someone has a creative solution for me here?

 

[CephasOz]

Hi momentumons. The same thing, but changed to allow for error conditions, so it's a bit longer.

Firstly, in ThisWorkbook, replace the current Workbook_Open with the following simple code:

' Workbook : Open
Private Sub Workbook_Open()
    ProtectAllWks
End Sub

Then, in a normal module, paste the following code:

' Protect all worksheets
Function ProtectAllWks() As Boolean
    Dim wks As Worksheet
    ProtectAllWks = True
    For Each wks In ThisWorkbook.Worksheets
        ProtectAllWks = (ProtectAllWks And ProtectSheet(wks))
    Next
End Function

' Password protect a given spreadsheet.
Function ProtectSheet(wks As Worksheet) As Boolean
    ProtectSheet = False
    On Error GoTo Err_Exit
    wks.Protect Password:="*********", UserInterfaceOnly:=True
    ProtectSheet = True
    Exit Function
Err_Exit:
    Err.Clear
End Function

Make sure that you replace the line of asterisks with the real password that you are using for the worksheets.

 

[momentumons]

Thanks @CephasOz I''m not sure why but that's not working for me....

I have two macros and neither of them will work unless I manually unprotect the sheets they impact... :/

(One is a power query and one is simply a copy/paste into another tab and amend the date it happened).

 

[CephasOz]

Hi momentumons. Okay, we're going to change the Workbook_Open code again, just to make sure what's happening. Paste the following code in ThisWorkbook.

' Workbook : Open
Private Sub Workbook_Open()
    If ProtectAllWks() Then
        MsgBox "All worksheets have been protected"
    Else
        MsgBox "Some worksheets have NOT been protected"
    End If
End Sub

Now, when the workbook opens you should get a dialog box popping up to say what has happened with worksheet protection. If you don't get a message at all, then the code is in the wrong place, and needs to be moved. To test it, save your workbook, close it, and re-open it. Do you see a dialog box? If so, what does it say?

 

[momentumons]

.

 

[momentumons]

Hi @CephasOz - thanks for not giving up one me!

I amended the code and I do see a pop up box when it opens. It says "All worksheets have been protected".

And.... now one of the macros works! Hooray! That macro was just one that I recorded that did a copy/paste/record date thing. Looks like that's working.

However, the "refresh power query" macros isn't working. It is registering because i does go to the right tab, but it doesn't update the data...

Getting closer