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:=True
In 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.
Sub ProtectSheet()
Sheet1.Protect Password:="123", UserInterfaceOnly:=True
End Sub
Thanks for reading the article, subscribe us to get more VBA tricks.
VBA Code to Find Last used Column or Row in Excel Sometimes as a developer, you need to take actions in Excel sheets based on last row or column. In Excel, there are two kinds…
This Excel VBA Code converts the excel range into HTML and also can convert Excel to HTML Table to paste data on Outlook Email Body
VBA Code To Add New Sheet In VBA, it is sometime important to add a worksheet at the right place in the Excel. Here is a simple an effective code that adds a new worksheet…
How to Add Outlook Reference in Excel VBA? To automate Outlook based tasks from Excel you need to add Outlook Object Library (Microsoft Outlook XX.X Object Library) in Excel References. You can follow below steps…
Custom Calendar Control for MS Access MS Access by default provides inbuilt functionality to pick dates using calendar control; however it lacks few basic functionalities which makes selecting a date bit difficult. For example, if…
How to Find Duplicate Files In excel using VBA? Yesterday I was working on my computer and cleaning the drives to make some more space. I was surprised to see so many files saved at…