Protect Excel Sheet for Manual Input but Allow Programming Inputs
Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? A common way to achieve this is using Unprotect function available in VBA before making changes to the sheet and then protect it back using Protect function. See below an example:
Sheet1.Unprotect "123"
Sheet1.Range("A1").Value = "ABC"
Sheet1.Protect "123"
While this code works well but has small limitation, the code will produce error when Excel file is in Shared Mode. This is because you are not allowed to protect or unprotect a sheet when shared mode is on.
Here is a simple code which can help you here:
Sheet1.Protect Password:="123", UserInterfaceOnly:=TrueIn the code you will notice that UserInterfaceOnly has been passed as True that does the trick. If you protect a sheet with UserInterfaceOnly as True then Excel allows VBA to write data on the sheet without unprotecting it.
- Press Alt+F11
- Insert a Module (Insert>Module) from menu bar
- Paste the code in the module
Sub ProtectSheet()
Sheet1.Protect Password:="123", UserInterfaceOnly:=True
End Sub
- Right click on the shape you have just added
- Click on ‘Assign Macro’ button
- Select ‘ProtectSheet’ from the list of macros
- Now clock on ‘OK’ button
- The code will protect the sheet with “123” password
- User will not be allowed to enter the details manually. See below the message user will receive when trying to update details manually
- When you will try to update details in the sheet using code, it will work without any error message
Thanks for reading the article, subscribe us to get more VBA tricks.
