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"
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.
Through formatting the cells, you can make your Excel data more beautiful and easier to understand. If you record a macro to add borders to Excel range, you will notice that it generates 30 plus lines of code for just a small work. Here we are sharing one line of code which does the same thing.
Random Rows Selector is an MS Excel based tool which can be used to pick random or stratified samples from a set of records available in the Excel. The tool is fully dynamic, it can support any data format in Excel.
Video: How to Hide Worksheet in Excel? Hide Sheet in Excel When I was creating an excel dashboard, there were multiple sheets which I used for calculation purpose and never wanted anybody to make any…
Dummy Data Generator is an MS Excel based tool which has capability of generating 45 types of data which includes numbers, text, date, time, Memo (long text), Boolean etc.
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…
Table of Content How does testing help? How should I test my tool or application? How does Dummy Data Generator tool helps in testing? How do I use this tool? From many years I have…