Security Question

[hello_world]

How can I set up my spreadsheet so that only an ADMIN can delete data. I already know about the Protection feature of excel, but it doesn't work quite the way I want.

[dominicb]

Good afternoon hello_world

I already know about the Protection feature of excel, but it doesn't work quite the way I want.

Not much information here.
What would be the way you want?

DominicB

[hello_world]

Thanks for the quick response.

Okay, here is the problem with the current Protection feature of excel. It is STATIC, meaning I have to specify which ranges I want so that it can not be modified. What I want is for it to be DYNAMIC, meaning if a user puts in Data, then the user can't delete the data (only an Admin) can.

Basically, I do not want to have to keep specifying ranges each time a user puts in new data (I don't want to expand a range).



A way I think this can be done, is with a macro. The macro will check to see if the cell has data in it, then change the cell to LOCKED so that the user can not reselect it. Is this solution viable?

[hello_world]

Also, I have noticed that with the Protection feature of excel... it messes up the features of a LIST... like if you click the list the bottom row no longer expands.

[dominicb]

Hi hello_world

Does this help. The code will need adding into the sheet module of your workbook and will keep tabs of where the cursor is at any given time and will lock each cell as an entry is made.

Please Login or Register  to view this content.

HTH

DominicB

[hello_world]

Hey,

That code does exactly what I want, but how would I set a password to protect the sheet. Also, could you add an if statement in there to see if the cell is empty, then change it to unlock.

I have one quirk tho. If protection is used on a sheet, then more rows to a LIST CANT be added. Is there a way to work around this?

[dominicb]

Hi

how would I set a password to protect the sheet

Alter the code I gave you above as follows :

Please Login or Register  to view this content.

to protect, and :

Please Login or Register  to view this content.

to unprotect.

could you add an if statement in there to see if the cell is empty

I don't think it needs it : I designed this to run on a sheet where all cells are set as unprotected, and every time something is entered in a cell, that one cell is protected. Do nothing, move around etc and the cell isn't protected. There does't need to be a check there... Of course if you have your own reasons shout out and we'll sort something out.

more rows to a LIST CANT be added

I'll have a look at this in the morning. In the meantime just describe to me how you add items to your list at the moment.

HTH

DominicB

[hello_world]

Thanks. You have been a great help.

The reason why I want there to be a check if the cell is EMPTY, is because what if the user accidentally clicks a cell and doesn't input data, and moves on... now he/she can't go back to the cell...

ALSO, is there a way to protect cells that have new data in them if the save button is hit? instead of automatically protecting after selecting a new cell?

And, the problem with LISTS and protected sheets is that in an unprotected sheet, if u click a list it will expand its box so that you can fill in a new row. However, if the sheet is protected, then the list will not expand.

Maybe i should give u a copy of my spreadsheet.

Basically, what I want to accomplish is the establishment of delete protection. Meaning, I only want admins to be able to delete data, but give users the access to input data.

[dominicb]

Hi hello_world

OK, we can't put a check in if a cell is empty because we have two checking points; once the cell is selected (when it will be empty) and once the data has been entered into a cell (when it won't be). But if a user selects a cell, decides it's not right and moves on that cell won't be protected. The same if a user starts entering and then presses escape. The data will only be committed after the user presses Enter or clicks on another cell.

As for the save idea, this is irrelevant because the sheet isn't protected after the user clicks on another cell but when the user presses Enter (or selects another cell, as in normal Excel mode). You could force a save which I wouldn't recommend, because (a) it's generally a bad thing to force saves - you're already losing your undo functionality by having a macro firing every time you change selection, you don't want to lose the ability to go back to the last save (b) the size of the file.

I've never actually used lists before but had a quick look and see what you mean. A trawl of the forums hasn't turned up anyway of getting around this other than removing the protection - which defeats your object. The only way around this would be to drop the lists and just have users updating the data in the normal "non-listed" fashion. Other than the asterisk indicating the next available row I can’t see any other benefit of using lists.

The concept of what you want to do in controlling who can delete data seems really simple, but it seems that within the confines of Excel you’re going to have to make a few compromises…

HTH

DominicB

[hello_world]

Yes. What I want to do seems very simple, and should be a feature that is included in the protection option of Excel.

The reasons why I use lists are because everything is brought down to the new row (Data validation, formulas, and color). You can sort the data or view certain data using lists (The toggles at the top). For example, I could use a list to view only certain projects, etc. The ability to sort on the fly and view certain items is very important.

I have thought about it and I have talked with someone else. We have come to a conclusion that having delete protection is a good thing to have, but there may be some downsides such as having to contact the administrator everytime you make a mistake. These mistakes can compile and give the administrator a lot of work.

We think a solution to this problem will be to save the workbook quarterly with a password lock. So that discrepancies can be easier to verify.

Thanks for your time, it is really appreciated.

[hello_world]

However, I think this is how a protection scheme could be set up if the protect feature of excel didn't mess up lists.

Upon a save (when the user clicks save), a macro can then be run so that it checks every cell to see if it had data in it. If the cell has data, lock the cell.