VBA to disable Delete or warn to clear only?

  • Let's just say that, hypothetically, you have a boss that -while wonderful - just can't leave the protection on worksheets! For one reason or another on a semi-weekly basis she feels compelled to "delete" something that needed "cleared" and then the formulas are messed up so she insists on turning off the protection...and well...it is all down hill from there!
    Hypothetical Boss also doesn't like being "treated like a child" by your asking her "what are you trying to do?" when she asks for the password.(Allow me to interject here to all of my co workers that I have using this forum, that the hypothetical boss is not anyone that they may know. Really.)
    So I was wondering...is there a way to, if someone right clicks and selects "DELETE" have a warning pop up and say "NO Delete, CLEAR CONTENTS ONLY!" and then not allow the delete? or maybe they would have to only delete from the toolbar?
    because I am about 20 minutes away from disableing the right mouse button and saying it is broken! *hypothetically* :furious: :)

    You can tell the quality of a person by how they treat people they don't need

  • Hi Kimberly

    This code may help. not too sure if it's Boss proof though :)

    Right click on the Excel icon, top left next to File and select View Code. In here paste this code

    Now go to Insert>Module and paste in this.

    Sub ClearAll()
    ActiveWorksheet.Protect UserInterFaceOnly:=True
    End Sub
  • Hi Dave! Thanks for the code!
    I followed your directions and it does what I wanted :thanx: . It does protect the worksheet as soon as I use it though. I can unprotect it, and sometimes it works, and sometimes it stays protected and I cannot add or clear any data.
    Any ideas on why?
    I tried to write the code on my own this morning, and after looking over yours I wasn't that far off...well far enough that it did ...well nothing lol...but I was on the right track!

    (on another note, love the Excel Hacks book! I am getting ready to buy my third copy! Seems the guys at work love it to, and it keeps getting borrowed...permanetly! lol)

    You can tell the quality of a person by how they treat people they don't need

  • I must be missing something. I can't see any change from what you gave me before. Sorry.
    Maybe I have typed it in wrong, I will try to cut n paste it, but so far it does nothing different.
    Could you highlight the change? Sorry, I am just trying to understand.

    Win xp, Excel 03!

    You can tell the quality of a person by how they treat people they don't need

  • Hi Kimberly

    Sorry, I should have said that I slipped the new bit in the original code as well. The original one had no ActiveSheet.Protect UserInterFaceOnly:=True in it intitially.

    I have now also worked out why it wasn't working for you. That is, the first right click deletes the Delete... menu item, then it can no longer get its Index number and fails.

    Try the original code now, tested and it works. The Delete... menu item is reset each time you select and cell(s).

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!