Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Limiting Who Can Delete Data.

Limiting Who Can Delete Data

by Allen Wyatt
(last updated January 13, 2021)

8

Jim has a workbook that is used by multiple people in his company. He wonders if there is a way to allow everyone to add data to a group of cells, yet restrict who can delete the data from the cells. He has a group of about 50 that he wants to be able to add data, but he wants to give the delete capability to just 2 individuals.

There are any number of macro-based solutions you can try. Essentially, you need a macro to detect when information has been deleted and then check to see if the person deleting the information has permission to do so. The following is just one possible approach to the issue:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sPassCheck As String
    Dim rng As Range
    Dim sTemp As String
    Dim sPassword As String

    sPassword = "Password"
    sTemp = "You must enter the password to delete data"

    'Use to set a single cell if more than one cell is
    'in the target range
    If Target.Count > 1 Then
        Set rng = Target.Cells(1, 1)
    Else
        Set rng = Target
    End If

    If rng.Value = "" Then
        sPassCheck = InputBox(sTemp, "Delete check!")
        Application.EnableEvents = False
        If sPassCheck <> sPassword Then Application.Undo
    End If

    Application.EnableEvents = True
End Sub

The macro, which is actually an event handler triggered whenever something in the worksheet is changed, checks to see if the information in a cell (or top-left cell in a range) was deleted. If so, then the user is asked for a password. If the person doesn't have the password, then the Undo method is invoked to "undo" the person's deletion. (You'll want to change the password, assigned to the sPassword variable) to the actual password you want people to use.)

Another option is to use an Excel add-in that can take care of the security issues for you. Some subscribers suggest using A-Tools, which comes in either a free or pro (paid) edition. You can find more information about this add-in here:

http://www.atoolspro.com/

A-Tools, among other things, apparently allows you to apply various security features to Excel data that resides on a network. (Chances are good that Jim is sharing his workbook on a network, as it is used by many people in his company.)

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11598) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Limiting Who Can Delete Data.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Cut and Paste Formatting

What happens when you copy information from one document and paste it into another? It is possible for what you paste to ...

Discover More

Adding a Drop Shadow to a Text Box

One way to make your text boxes "stand off" the page is to add a drop shadow to them. This tip shows just how easy it is ...

Discover More

Setting Cell Color Based on Numeric Values

Excel allows you to specify colors for the interior of cells in your worksheet. If you want those colors to be set ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Removing Protection from a Protected Workbook

Excel provides built-in capabilities to protect your workbook files. If you apply these capabilities, it is possible that ...

Discover More

Protecting a Workbook from Opening in Other Programs

When working with sensitive information, you always need to be concerned that your data doesn't fall into unwanted hands. ...

Discover More

Saving a Workbook Using Passwords

If you want to protect your workbook so that others cannot open or change the information it contains, an easy way to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 2?

2021-01-25 14:29:37

J. Woolley

@David Bonin
Thank you for showing me how to indent VBA when commenting on this site. That has frustrated me for years.


2021-01-18 10:20:20

David Bonin

Chuck,

I, too, use indenting when I have complex formulas, particularly when splitting a long formula into smaller pieces would add confusion. Sometimes, many smaller formulas becomes like a preacher reading a holy text, pausing after every couple words, so that by the time he gets to the end of the sentence you forgot what he started the sentence with.

Unfortunately, adapting techniques situationally often looks like hypocrisy. So be it.

I think you can use non-breaking spaced to indent text on this site. Let me try. I think this will work.

Normal text
    Preceded by four non-blanking spaces
        Preceded by eight non-blanking spaces
        Preceded by four [non-blanking space / normal space] pairs

A non-blanking space is the Alt-0160 combination. Hold the Alt key down while you type 0160 on the numeric keypad.

I find non-blanking spaces are also nice when you want to tweak some text in Word or PowerPoint. Using a non-blanking space between two words will keep them together. This can be nice when you don't want a line of test within a narrow paragraph to end in, say, a two-letter word sticking way out to the right.


2021-01-16 12:13:22

Chuck Trese

Peter,
You can also break your formula into separate lines if you wish. Enter a new line by pressing Alt-Enter - the same way you would do it in a cell. Although I rarely use it, this can be really helpful for longer more complex (nested) formulas. In the simple case of the formula given by David for example, it can be written this way:

=IF(SQRT(A1^2 + B1^2)>=25,
C1,
(A1+B1+C1)/(2*D1)
)
I expect this site to remove the indenting in the above, so you'll want to indent lines appropriately. By doing this, you can make the formula resemble vba code, in the common If-Then-Else format.

David,
I also love efficiency, but agree with you that sometimes it doesn't really matter, and just move on, as you said. Having said that, I have learned a lot from Willy over the years. And I think this site is all about learning, and learning techniques or little tricks to code more efficiently is a big part of that. I have found that once one learns to think/code more efficiently, it doesn't become extra effort, it becomes less effort. And simple (efficient) code is usually easier to understand than unnecessarily complex code. I had to agree with Willy that the code presented in this tip was quite involved for such a simple concept ("If Target is blank, then Undo").

I'm pretty sure Allen Wyatt would say this site is all about teaching - often beginners - so those extra (unnecessary) lines are also teaching. Things like dimensioning your variables, adding comments, pointing out that the Target can be more than 1 cell, and a method to handle that, and so on. Also valuable.

Anyway, hope none of these points above come across as arrogant or combative. I mean it exactly the opposite. Cheers to us old dudes.


2021-01-16 06:54:52

Peter Atherton

Dave Bronin
Very interesting post, I hadn't realised that spaces were allowed in a formula. The first time I tried it, on an existing formula, Excel was confused and changed it back to the original - but it was OK after. I'll try this more often in future.
Thanks for the tip!


2021-01-15 10:37:03

David Bonin

As an engineer, I value efficiency. As an old engineer, I do not value efficiency over all else.

Some code is complex, takes a while to run, and will have an impatient user waiting for its conclusion. Sometimes me is that user. I often put extra effort towards making that code more efficient, often using more sophisticated techniques and sometimes even testing and timing alternate methods. Of course, this development takes more time and mental energy on my part and the code may be more complex.

Some code is simple enough or run infrequently enough that quick code writing is good enough. Here, better coding techniques and efficiency are not worth the extra effort.

As I age, it's becoming more and more likely that someone else may have to edit that code in some future year. Someone not me. Someone who may be less skilled than me. In that case, I opt for more documentation and more simplicity.
___

On a related note, I often apply the same philosophy to workbook formulas. Writing a great mega-formula isn't worth it if the next workbook mechanic can't understand it. So I break things down where I can.

Finally, one thing I have never understood is why most users don't include any spaces in their formulas. The following two formulas are the same except one is for more readable:
=IF(SQRT(A1^2+B1^2)>=25,C1,(A1+B1+C1)/(2*D1))
= IF( SQRT( A1 ^ 2 + B1^2 ) >= 25, C1,( A1 + B1 + C1 ) / ( 2 * D1 ))

I mean, wedon'twritesentenceswithallofthespacespulledoutsowhydowedoitwithExcelformulas?
I have never been able to detect any increase in execution time from the spaces, and the file size increase is very small.


2021-01-14 11:30:20

Willy Vanhaelen

The macro in this tip is not well conceived.

Testing Target to see if it is only one cell or a multiple cell range is unnecessary. Target(1) will do the job nicely in both cases.

Declaring sTemp and sPassword as string variables and then assigning text to them that the macro will not change can be done simpler by using the Const statement.

Here is my 8 lines version which does the job equally well as the 17 lines macro of this tip

Private Sub Worksheet_Change(ByVal Target As Range)
If Target(1).Value <> "" Then Exit Sub
Const sPassword = "Password"
Const sTemp = "You must enter the password to delete data"
If InputBox(sTemp, "Delete check!") <> sPassword Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
End If
End Sub

The purpose of the macro is to prevent not authorized people to delete cells. But by making any entries in any cells will trigger the macro as well. In this tip's macro the test is only done in the middle (If rng.Value = "" Then ...) and the previous code is executed each time an entry is made for no purpose at all.

In my macro the first line tests to see if a deletion is done. In all other cases the macro is terminated at once. This is more efficient of coarse.


2021-01-13 14:25:10

Chuck Trese

very similar to David's suggestion, I have used this:

If Application.UserName <> "Trese Chuck" Then
Call MsgBox("Sorry, this feature is not currently available to you.", vbCritical, "PutMsgBoxTitleHere")
Exit Sub
End If

Put that at the beginning of the function/sub. If the user is not the right person, then they just get (politely) kicked out of the subroutine.

As David indicated, that will keep the honest people out. Clever ne'er-do-wells can easily get around it.
(btw, David's logic was backwards in his example, but that's an easy fix)


2021-01-13 11:24:52

David Bonin

Passwords are a pain, though they can be helpful in doing a "Do you really want to delete?" check.

Here's a seamless check. Let's say that you want to allow Cindy Smith to delete workbook data. You can choose to allow Cindy to delete data by checking her Application.UserName. Simply include a "test for Cindy" in your macro, something like:
If Application.UserName <> "Cindy Smith" Then
Do Cindy's stuff
Else
Do something else
End If

You can readily expand this to a whole list of authorized people if you like with a few "and" operators or using "Instr()".

Of course, users could temporarily rename themselves to "Cindy Smith" to delete data, but users that smart can also likely work around macros. So I wouldn't worry too much about it.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.