Macros with protected sheets

momentumons

New Member
Joined
Mar 30, 2020
Messages
48
Office Version
2016
Platform
Windows
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! :)
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,627
Office Version
365
Platform
Windows
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

VBA Code:
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

New Member
Joined
Mar 30, 2020
Messages
48
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,627
Office Version
365
Platform
Windows
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
VBA Code:
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

New Member
Joined
Mar 30, 2020
Messages
48
Office Version
2016
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,627
Office Version
365
Platform
Windows
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

New Member
Joined
Mar 30, 2020
Messages
48
Office Version
2016
Platform
Windows
@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

New Member
Joined
Mar 30, 2020
Messages
48
Office Version
2016
Platform
Windows
Oh haha, right... :p

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

momentumons

New Member
Joined
Mar 30, 2020
Messages
48
Office Version
2016
Platform
Windows
Oh and yes everything should have the same password
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,627
Office Version
365
Platform
Windows
Is the workbook structure protected ?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,262
Messages
5,443,397
Members
405,234
Latest member
AA90

This Week's Hot Topics

Top