Prevent Cell Deletion but allow editing and inserting

[Kelvinjp]

Hello,

I am looking for a solution that would prevent users from deleting a given cell (shift down, up, left, etc.) to prevent shuffling of data across the sheet but I still would like the ability to have users to edit cells and insert rows.

Any solutions?

Thank you in advance for your help!

[AC PORTA VIA]

is this what you are looking for

Please Login or Register  to view this content.

]

[excelgirl84]

In Excel VBA, you can prevent users from deleting a given cell by using the Protect method of the Range object. This method allows you to specify which actions users are allowed to perform on the protected cells, and which actions are not allowed.

For example, the following code will protect a given cell (in this case, cell A1) and prevent users from deleting it:

Please Login or Register  to view this content.

The Protect method has several arguments that you can use to customize the protection behavior. In the code above, the Password argument is used to set a password that users must enter to unprotect the cells. The UserInterfaceOnly argument is used to specify that the protection applies only to user actions performed through the Excel user interface, and not to actions performed by VBA code. The AllowFormattingCells argument is used to allow users to format the cells (e.g., change the font or background color), and the AllowDeletingRows argument is used to prevent users from deleting rows containing the protected cells.

Note that this solution will only prevent users from deleting the protected cell by using the "Delete" command. It will not prevent users from shifting the cells up, down, left, or right, or from deleting the cell by using other methods (e.g., by using the Cut or Clear commands). To prevent these actions, you will need to use additional VBA code to intercept and cancel them.

You can find more information about the Protect method and the available arguments in the Excel VBA documentation. To access the documentation, in Excel, press Alt+F11 to open the Visual Basic editor, then go to Help > Microsoft Visual Basic Help. In the search box, type "Range.Protect" and press Enter to see the documentation for this method.

[TMS]

@excelgirl84: please can you provide the reference for rng.Protect.

AFAIAA, you lock or unlock cells/ranges. You Protect worksheets (and workbooks).

[excelgirl84]

@excelgirl84: please can you provide the reference for rng.Protect.

AFAIAA, you lock or unlock cells/ranges. You Protect worksheets (and workbooks).

rng.Protect is a method of the Range object in Microsoft Excel that can be used to protect the cells in a range. The Protect method takes a number of arguments that can be used to specify which operations should be allowed on the protected cells and to set a password to unprotect the cells.

In the example above, the rng variable represents the cell at A1, and the Protect method is used to protect that cell with the password "mypassword". The UserInterfaceOnly argument is set to True, which means that the protection will only apply to changes made through the user interface, and not to changes made through VBA code. The AllowFormattingCells argument is set to True, which means that the user will be able to format the cells in the protected range. The AllowDeletingRows argument is set to False, which means that the user will not be able to delete rows in the protected range.

[TMS]

@excelgirl84: have you tried to run the code you provided?Error 438 Message.jpg