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:

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.

Protect Excel Sheet for Manual Input
Add a Shape: You need to run this code only once when the Excel file Shared Mode is off. So it would be better to add a shape in your Excel file such as below
Protect Excel Sheet for Manual Input
Insert VBA code in module:
  • Press Alt+F11
  • Insert a Module (Insert>Module) from menu bar
  • Paste the code in the module
Sub ProtectSheet()
    Sheet1.Protect Password:="123", UserInterfaceOnly:=True
End Sub
Protect Excel Sheet for Manual Input
Link the code with shape
  • Right click on the shape you have just added
  • Click on ‘Assign Macro’ button
  • Select ‘ProtectSheet’ from the list of macros
  • Now clock on ‘OK’ button
Protect Excel Sheet for Manual Input
  • The code will protect the sheet with “123” password
  • User will not be allowed to enter the details manually. See below the message user will receive when trying to update details manually
Protect Excel Sheet for Manual Input
  • When you will try to update details in the sheet using code, it will work without any error message
Protect Excel Sheet for Manual Input
Protect Excel Sheet for Manual Input

Thanks for reading the article, subscribe us to get more VBA tricks.

Leave a Reply

avatar
VBA Code to Count Cells by Color
VBA TRICKS
VBA Code to Count Cells by Color

Have you ever felt that Microsoft should have added a formula in Excel which can count the cells based on specific color? I have seen many code requests to share a VBA code that can count the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to count the cells with specific color and returns the count of the matching color cells.

VBA Code to Sum Cells by Color
VBA TRICKS
VBA Code to Sum Cells by Color

Recently some of our subscribers have requested us to share a VBA code that can sum the cells by its color. To help our subscribers and developers, we are sharing 2 codes that be used to sum the cells with specific color and returns the total sum of the matching color cells

Time and Motion Study Thumbnail
FREE VBA UTILITIES
41
Time and Motion Study – Complete Guide

Here is one more wonderful free tool from ExcelSirJi.com which makes your life easy. Time & Motion Tracker helps you to track Start and End time of any type of transaction or activity. The good thing is, it is VBA based tool which helps you to protect from manual manipulation in the data by the user. It is also easy to use, just click on Start (shortcut: Ctrl+Shift+A) or Stop (Ctrl+Shft+S) buttons to record the time

error

Like the Article? Please spread the word :)

Facebook
Facebook
YouTube
YouTube
Pinterest
Pinterest
LinkedIn