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.
Please note that this code needs to be executed once before you share the file.
What is the Usage of sheet color in Excel? When we prepare a report or a dashboard it is easy to identify or analyze reports with a change of color sheet tabs. Analysts generally give…
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…
VBA Code to send Outlook Emails Sending bulk emails is a very common activity, there are many office activities that need a person to send bulk emails to single or multiple recipients. You also may…
Table of Content VBA Code to Get User Domain Name VBA Code to Get User Domain Name – Method 1 VBA Code to Get User Domain Name – Method 2 Steps to use this VBA…
Have you ever got into situation in office where you need to count the cells in Excel sheet with specific color? If yes then you can use following code which counts the number of cells…
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