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!

 

[momentumons]

@CephasOz hmmm yep. it's weird. If I add the

, DrawingObjects:=False, AllowFiltering:=True

Then the whole workbook becomes 100% editable - the tabs still say they're protected but you can edit/delete everything! If I delete that little bit of code it goes back to being fully protected.. (with the saving/re-opening).
Any ideas?

 

[momentumons]

It's the Drawing Objects addition - only having the AllowFiltering works...

 

[momentumons]

Oh hey, me again! I can't get the "DrawingObjects" part to work. But i did find another workaround for my problem (I realised I had the object "locked" doh! I didn't know you had to unlock objects as well as cells!)

 

[CephasOz]

Well done. The good thing about a workaround is that you've got it working.

 

[momentumons]

Thanks again @CephasOz ! I have another Q for you re: this macro. How can I allow people to group/ungroup cells while the sheet is protected? I can't see an option using the "Allow" codes I used previously to allow sorting/formatting etc. I've googled it and can see there's some sort of ".EnableOutlining" code - but I've tried to pop it into the existing macro in a few spots and I get errors! Can you help?

 

[CephasOz]

Hi momentumons. It seems that you have to use .EnableOutlining before you protect the worksheet. So you need to:

1) Unprotect and setup outlining in the worksheet(s).
2) Replace the ProtectSheet function with the code below (making sure to replace the asterisks with your real password).
3) Save the workbook, close it, then re-open it to have the protection in place with outlining.

Function ProtectSheet(wks As Worksheet) As Boolean
    Const cstrPassword As String = "********"
    ProtectSheet = False
    On Error GoTo Err_Exit
    wks.Unprotect Password:=cstrPassword
    wks.EnableOutlining = True
    wks.Protect Password:=cstrPassword, UserInterfaceOnly:=True, DrawingObjects:=False, AllowSorting:=True
    ProtectSheet = True
    Exit Function
Err_Exit:
    Err.Clear
End Function

 

[momentumons]

@CephasOz you are a legend! Thanks so much. This is the best spreadsheet I've ever made and I have learnt so much through it. Thank you

 

[CephasOz]

Glad to help.