Advising Users they shouldn't delete info

[nhrav]

Hi, I have a workbook where it is important that users do not delete any information in a particular range. But, I do not want to 100% block users from deleting the info.

What I would like to do is, if a user selects a cell within the range, and tries to overwrite or delete, that a warning box comes up to advise the user against changing the data. Is there a way to do this? Thanks.

[BigBas]

This example code displays a message box that asks the user to confirm if they want to make changes. If they choose no, it undoes the change. Note: This code is setup to only work on Column B. Test it out and report back.

Please Login or Register  to view this content.

[MickG]

Hi nhrav
Place this code in a SelectionChange Event as Shown.
Change the Range Address and Wording to suit.

Please Login or Register  to view this content.

Regards Mick

[nhrav]

OK, thanks to you both! That looks like it should work for me. But I do have another question (my Step 2)!

Let's say my data exists in A2:Y272. I will protect the data as you have shown me above. Every year, I add rows below 273, 274, etc. Then, I sort Column F, run a Vlookup, Sort Column G, run a Vlookup, on an on until Column W (all in a macro, already written).

The data that was originally in A2:Y272 will mix in with the new data. I want to continue to protect the old data, while leaving the new data "fixable". Is there a way to 'track' the original rows as they are sorted, so that the message box pops up only when the data in the original data set is changed?

Example: Cell G64 ("Hello") is in my original data set. Then, Cell G273 ("Goodbye") is added. Column G is sorted, and the cell that was G64 ("Hello") is now G2. The cell that was G273 ("Goodbye") is now cell G3. A user tries to change cell G2 ("Hello"). I want a message box. A user tries to change cell G3 ("Goodbye"). No message box.

Possible?

[MickG]

Hi, How about setting the font colour for the seleted area to "Blue", then that font colour will always be blue for that cell whether it moves or not.
By using the code below, the msgbox will appear if the active cell font is "Blue"

Please Login or Register  to view this content.

What do you think??
Regards Mick

[nhrav]

Very creative solution, I think I'll try something along those lines!

[nhrav]

Hi, How about setting the font colour for the seleted area to "Blue", then that font colour will always be blue for that cell whether it moves or not.
By using the code below, the msgbox will appear if the active cell font is "Blue"

Please Login or Register  to view this content.

What do you think??
Regards Mick

Hi, will the message box still pop up if the macro is not running? I'm not to familiar with Private Subs. Thanks.

[MickG]

Hi, Because the Macro is in a "SelectionChange" Event (as it should be !), each time you change the Cell Selection in the worksheet, the Macro runs.
Obviously, If the Criteria is not met (Active Cell Font not "Blue") you won't see anything, although the Macro has run.
Regards Mick

[nhrav]

Ok. One more question before I put the code into the worksheet. Will the MsgBox pop up if the user selects the cell in question, or if the user tries to change the contents of the cell in question? I'm looking for the latter.

Thanks.

[MickG]

Hi,nhrav
From what you now say, you could achieve all this by protecting these cell. Have a look at "Excel", "Help", "Protection".
If you protect the cells this way, when you try to alter them, you will immediately get a Msgbox to Stop them being altered.
You can also add a Password to further increase your control
With regard to the existing code, you can't really alter it to achieve the same result as "Protection".
Regards Mick

[nhrav]

Mick G, I just went through Excel's process of protecting a range. I selected all cells, and in Format > Cells > Protection, unlocked them. Then, I selected the range I wanted to protect, and went back to Format > Cells > Protection, and locked that range. Then, I went to Tools > Protection > Protection Sheet.

I tried to edit cells in the range, but was prevented. I tried to edit cells out of the range, and was allowed. That's what I wanted

But, when I inserted a row in the middle of the data, and tried to enter data into the empty cells, it wouldn't let me because those cells were locked.

Additionally, I added another cell at the bottom of the range and tried to sort the data, and it wouldn't let me because the cells were locked. However, when I Protected the sheet, I said it was OK for users to Sort Data. Know what might have went wrong?

[MickG]

Hi,nhrav
I'm sorry my knowledge of "Protection" is quite limited, and I get the same problem as youself, you perhaps need to ask the question again to enablle someone else to answer.
Regards Mick

[nhrav]

*bump*

OK, anyone else want to take a shot?