Allowing VBA code to make changes, even when sheet is protected

[pijan11]

I'm using this code to allow to make changes to a protected sheet with a VBA code:

Sub AllowVBAChangesOnProtectedSheet()

'Enable changes to worksheet by VBA code, even if protected
Sheets("Sheet1").Protect Password:="myPassword", _
    UserInterfaceOnly:=True

End Sub

Every time I close my excel file and return to it, I always have to run this code manually before inputting any data in my spreadsheet. If I don't I get a Run-time error '1004'.

Any help would be appeciated.

 

[Peter_SSs]

Welcome to the MrExcel board!

Remove that code and put this code in the ThisWorkbook module instead.

Private Sub Workbook_Open()
  'Enable changes to worksheet by VBA code, even if protected
  Sheets("Sheet1").Protect Password:="myPassword", _
    UserInterfaceOnly:=True
End Sub

BTW, when posting vba code, please use the available code tags. My signature block below has more details. I fixed it for you this time in your post above.

 

[pijan11]

Welcome to the MrExcel board!

Remove that code and put this code in the ThisWorkbook module instead.

Private Sub Workbook_Open()
  'Enable changes to worksheet by VBA code, even if protected
  Sheets("Sheet1").Protect Password:="myPassword", _
    UserInterfaceOnly:=True
End Sub

View attachment 56455

BTW, when posting vba code, please use the available code tags. My signature block below has more details. I fixed it for you this time in your post above.

Thanks Peter. It's working. Now how would I add another code so that it does the same thing to another protected sheet ?

 

[Peter_SSs]

If the other sheet is in the same workbook but has a different password, you could use

Private Sub Workbook_Open()
  'Enable changes to worksheet by VBA code, even if protected
  Sheets("Sheet1").Protect Password:="myPassword", UserInterfaceOnly:=True
  Sheets("Other Sheet").Protect Password:="otherPassword", UserInterfaceOnly:=True
End Sub

If there were multiple sheets and they all used the same password a loop could be used to apply the protection.

 

[pijan11]

If the other sheet is in the same workbook but has a different password, you could use

Private Sub Workbook_Open()
  'Enable changes to worksheet by VBA code, even if protected
  Sheets("Sheet1").Protect Password:="myPassword", UserInterfaceOnly:=True
  Sheets("Other Sheet").Protect Password:="otherPassword", UserInterfaceOnly:=True
End Sub

If there were multiple sheets and they all used the same password a loop could be used to apply the protection.

And how do you apply a loop? Sorry for all the questions. I’m a beginner.

 

[Peter_SSs]

First, make sure all the passwords for the relevant sheets are the same then

Private Sub Workbook_Open()
  'Enable changes to worksheet by VBA code, even if protected
  Dim itm As Variant
  
  For Each itm In Array("Sheet1", "Other Sheet", "Data")
    Sheets(itm).Protect Password:="myPassword", UserInterfaceOnly:=True
  Next itm
End Sub

 

[pijan11]

Thank you very much Peter. Since you're the only one replying to me (and I really appreciate it) would you mind looking at my other post entitles Excel Running Slow?

Excel running slow

Hello, I'm new to VBA and I need some help with speed being slow when inputing data into my file (sorry for my data being in French). When I input data in the Fields entitled "École", "Année scolaire", i can't do anything for about 4 seconds. When I input data into the field "Nombre...

www.mrexcel.com

If you have time

 

[Peter_SSs]

would you mind looking at my other post entitles Excel Running Slow?

. I try to look at as many threads as I can, and if I come across yours and think I can help, I will.