VBA Excel - Use UserForm even if the sheet is protected

[cjvdg]

Hello! I just want to ask if can I use a macro or UserForm even if the sheet is protected or should I just leave the sheet unprotected? I have an UserForm with a CRUD function, can I use it even if the sheet is protected? If yes, how can I do that? Can someone give me an idea? Thank you!

[jolivanes]

If protection interferes with anything, at the beginning of the code, unprotect, run your macro and at the end protect again.
Lots of examples on this forum on how to do that.
for instance



Sheets("Sheet1").Unprotect Password:="myPassword"


'Your code here


Sheets("Sheet1").Protect Password:="myPassword"

[cjvdg]

If protection interferes with anything, at the beginning of the code, unprotect, run your macro and at the end protect again.
Lots of examples on this forum on how to do that.
for instance



Sheets("Sheet1").Unprotect Password:="myPassword"


'Your code here


Sheets("Sheet1").Protect Password:="myPassword"

Oh. So I need to put the password every time I'm going to do something with the sheet? OKay thank you!

[snb]

Please, do not quote !!

Avoid 'protection' in Excel: it serves no purpose.
A thoroughly designed structure of data in a worksheet, structure of presentation in another worksheet and structure of User Interaction (Userform) is vital & crucial.

[Paul_Hossler]

Please, do not quote !!

Avoid 'protection' in Excel: it serves no purpose.

A thoroughly designed structure of data in a worksheet, structure of presentation in another worksheet and structure of User Interaction (Userform) is vital & crucial.

1. Quote only when necessary and only what is necessary - not the whole entire post

2. Protecting a WS and unchecking "Select Unlocked Cells" allows the Tab key to move to next input cell, providing a better user exerience

3. Depending on complexity, a UserForm may provide an easier to use and more robust application

[p45cal]

You can avoid the repeated unprotect/protect operations by using UserInterfaceOnly:=True as part of the Protect line.
It stays in force until the workbook is closed or until another Protect operation cancels it.
You could for example place it in the workbook_open event:
Private Sub Workbook_Open()
Sheets("Sheet1").Protect Password:="myPassword", userinterfaceonly:=True
End Sub
This allows vba to alter a sheet but not the user.