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 - just trying to give you heaps of info so you can help me... re: the macro not working:
- I have assigned the macro to a button in the "INSTRUCTIONS" tab, I click it and it goes to the "DATA" tab where I recorded myself pressing Ctrl alt f5 to update the power query table from another excel doc in the same folder.
- When everything is unprotected this works beautifully
- When the sheet(s) are protected, I can still press the button, It takes me to the 'DATA' tab, but the table doesn't refresh

Help! Please! )

(Also, not sure why the other macro has just started working?)

 

[CephasOz]

Okay, I think I know what's going on. By using SendKeys to update the power query table, the protection is stopping that because keypresses are part of the user interface. So, I think that you need to have a direct VBA method to perform the update, rather than a VBA method that switches to a user interface method. Comment out the line with the SendKeys instruction, then add this line:

ThisWorkbook.RefreshAll

 

[momentumons]

Thanks @CephasOz I've tried that but nothing happened... with this new code it doesn't even go to the tab or anything... ?? This is the 'refresh' code currently:

Sub REFRESH_DATA_TAB()
'
' REFRESH_DATA_TAB Macro
'

'
    ThisWorkbook.RefreshAll
End Sub

 

[Yongle]

You have now gone full circle!
This appears to be a Power Query issue rather than a protect/unprotect issue
Consider starting a new thread on the Power BI forum which is the apporopriate forum for your problem

 

[momentumons]

Thanks so much for all your patience and your help @Yongle and @CephasOz . I've learnt a lot and my spreadsheet is looking and functioning so much better. I really appreciate it

 

[momentumons]

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.

Hi @CephasOz. I LOVE this macro you wrote for me to protect all sheets. It's a lifesaver. I have a Q about it now. I need the user to be able to edit a shape (text box) and use a filter while the sheet is protected. I can do it using the 'right click - protect functions to allow editing shapes and sorting functions'. However, when I save the file and exit these preferences are not saved. Is this because the PROTECT macro is overwriting it when it opens? Can you help me?

 

[CephasOz]

Hi again momentumons.

Take a look at the Microsoft page Worksheet.Protect method (Excel)

So, if you replace the current ProtectSheet function with

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

that should give you the functionality that you need. (Again, make sure you replace the asterisks with your password.)

 

[momentumons]

@CephasOz you are a legend. That one works perfectly. Thanks so much!

 

[momentumons]

@CephasOz actually - that seems to allow everything in the whole workbook to be edited as though there was no protection/locked cells at all :/

 

[CephasOz]

Hi momentumons.

It works on my workbook. Did you replace the asterisks in the new ProtectSheet? And remember that the protection is being applied when the workbook is opened, so you'll need to either run ProtectAllWks yourself, or save, close, and re-open the workbook.

Also, I noticed that I incorrectly included AllowSorting instead of AllowFiltering, so before you do anything else, change AllowSorting to AllowFiltering in the code.