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!

 

[Yongle]

You need a line to unprotect the sheet before anything is done and another one to protect the sheet when the macro has effected the necessary changes

Sub TestSub()
    Const pWord = "CaseSensitivePassword"
    Dim ws As Worksheet
    Set ws = Sheets("Name of sheet")
   
    ws.Unprotect pWord
   
    'the doing bit of your macro goes HERE

    ws.Protect pWord 
End Sub

 

[momentumons]

Thank you @Yongle but i couldn't make it work...

This is my code:

Sub REFRESH_DATA_TAB()
'
' REFRESH_DATA_TAB Macro
' unprotect DATA sheet refresh power query ctrl alt f5 protect workbook (pwd: XXXXXX)
'

'
Const pWord = "XXXXXX"
Dim ws As Worksheet
Set ws = Sheets("DATA")

ws.Unprotect pWord

Sheets("DATA").Select
ActiveSheet.Unprotect
ActiveWorkbook.RefreshAll
Sheets("DATA").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

ws.Protect pWord

End Sub

What have I done wrong? When I activate the Macro it says "the cell you're trying to change is on a protected sheet..." and nothing happens... ? ;/

Can you help?
Thanks

 

[Yongle]

In future please use code tags when posting code to make it easier to read
Click on <vba/> and paste code between the tags
(Click on <rich/> if you want to add your own formatting to the code in any way )

Various message boxes added to help you diagnose your problem
Remove unecessary lines after testing

Sub REFRESH_DATA_TAB()
    Const pWord = "XXXXXX"
    Dim ws As Worksheet
    Set ws = Sheets("DATA")

'get status of all sheets
Dim myStr As String, sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
    myStr = myStr & vbCr & sh.Name & " protected = " & sh.ProtectContents
Next sh
MsgBox myStr, , ""
   
'unprotect DATA
    ws.Unprotect pWord
'get status of DATA
MsgBox ws.Name & " protected = " & ws.ProtectContents
'refresh tables
    ActiveWorkbook.RefreshAll
'protect DATA
    ws.Protect pWord
'get status of DATA
MsgBox ws.Name & " protected = " & ws.ProtectContents


End Sub

Refresh All
What else in the workbook is being refreshed by RefreshAll ?
Are any of the affected sheets also protected?
ws.ProtectContents returns TRUE if sheet is protected

 

[momentumons]

Thanks so much for your help
Unfortunately I haven't solved it.
When I tested the above code I had 3 issues:
- It came up with a message box telling me true/false which sheets in my workbook are protected (annoying! How can we remove?)
- It came up with an additional two message boxes telling me the DATA sheet was unprotected and then protected again (annoying - can we remove?)
- Unfortunately the sheet did not refresh ;/

Any ideas?

 

[Yongle]

Stop moaning , the message boxes are there to help you and are telling you EXACTLY what you need to know and can be removed AFTER you have solved the problem

- It came up with an additional two message boxes telling me the DATA sheet was unprotected and then protected again (annoying - can we remove?)
it CONFIRMS that UnProtect\Protect on the sheet is working ✔

- It came up with a message box telling me true/false which sheets in my workbook are protected (annoying! How can we remove?)
- WE NEED THIS INFO
- which sheets are protected OTHER than DATA ?
- are all sheets protected with the same password?

 

[momentumons]

@Yongle Sorry I forgot to answer your Qs:
- Nothing else should be refreshing with this macro. I simply want to refresh the power query in this specific DATA sheet that checks and grabs the latest data from another workbook
- Everything can happen in this one sheet if I can work out how to do it!

 

[momentumons]

Oh haha, right...

No other sheets are CURRENTLY protected other than data... but they will be...

 

[momentumons]

Oh and yes everything should have the same password

 

[Yongle]

Is the workbook structure protected ?