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

Printing Summary Information from a Macro

Part of the information that Word maintains about each of your documents is a summary statement, which you can define in ...

Discover More

Deleting Tab Stops

Need to delete some tabs tops in a paragraph? It's easy to do using the Tabs dialog box, as described in this tip.

Discover More

Calculating a Future Date

Need to figure out a date a certain number of days, weeks, months, or years in the future? It's easy to do using the ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Using Strong Workbook Protection

Need to protect the data in your workbook so that others can't get at it? Here are some ideas on how you can approach the ...

Discover More

Always Opening a Workbook that is Editable

When you send a workbook to a coworker, it can be bothersome if that person has problems using what you created. There is ...

Discover More

Protecting an Entire Workbook

Want to stop other people from making unauthorized changes to your workbook? Excel provides a way that you can protect ...

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 two more than 3?

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.