VBA Code to 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:
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.
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.
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 you want to select a date…
Excel supports more than 16 million colors in a cell; hence you should know how to set the exact color in a cell. To do this, you can use RGB (Red, Green, Blue) function in...
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 to add Outlook reference in Excel VBA: 1….
Here is another help code for programmers to change the color of Excel sheet tab. I have shown two ways to achieve this: Using standard colors like Green, Blue, Red, White etc. Using RGB (Red,…
Sending bulk emails is very common activity, there are many office activities which needs a person to send bulk emails to a single or multiple recipients. You also may require to add attachments in the…
In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location