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.
Colorindex in Excel VBA Today let’s try to understand how ColorIndex property in Excel VBA works. It is an easy and effective way to quickly complete the development. ColorIndex property is normally used by VBA…
VBA Code to Count Color Cells With Conditional Formatting Have you ever got into situation in office where you need to count the cells with specific color in conditional formatted Excel sheet? If yes then…
Free File Renamer Tool – Quickly Rename files batch using Excel VBA Here is another help code and tool for programmers to rename files. You can use this tool for renaming all files available in…
VBA code that will sum cells by its color through excel function. This code will really help in making the analysis and presentation better.
VBA Code to Sort Data HERE IS A ONE LINE CODE FOR DEVELOPER’S REFERENCE WHICH CAN BE USED TO SORT DATA ‘Sort data in ascending order on Column F (Created At) Sheet1.Range(“A1:G” & Sheet1.Cells.SpecialCells(xlCellTypeLastCell).Row).Sort Key1:=Sheet1.Range(“F1”),…
How to use VBA to open Workbook in Excel? There are few VBA codes which are commonly used by every developer. One of them is giving an option to user to browse a file. Below is a…