Limiting Who Can Delete Data

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

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.

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.

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!

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.

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.

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)

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.