Delete entire content if cell A1 value change

Solved/Closed
Nitin - Jun 10, 2010 at 08:32 AM
 Nitin - Jun 12, 2010 at 01:20 AM
Hello,

I have been enriching high value support in excel programing, its great upto you guys,
I have a another chanllange to answer, hope you solve these one as usal,

I need to make a particulat cell as "Key",
I have worksheet called "WIP", in which cell M12 as key, I have my e-mail id entered in that cell

I need to run code, on worksheet change function of course, if anybody tries to change or delete the e-mail id already entered, entire content of that worksheet should get deleted & worksheet should get saved automatically.

Please note I have certan other macro & "on calculation" event running on same worksheet, so do need to set "Application.EnableEvents = False" prior to that,

Please help guys ....

1 reply

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 10, 2010 at 11:12 AM
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too


ALSO AND IT IS IMPORTANT SO THAT IS WHY I AM USING CAPS

On change property also gets triggered, you do nothing but just hit the ENTER button on that cell. So if you were in edit mode in that cell and then clicked ok without even making any change, the ON CHANGE event WILL BE triggered.
0
Hi rizvisa
thanks for your reply,

please note link of the file
http://wikisend.com/download/444592/Copy of Task Management-z.xls

To explain further, if you go to sheet WIP cell M12, you will find my e-mail address.

I want nobody to delete my e-mail id, so I want to write some script in "on calculation" event or "on change" event, that, if anybody tries to change or delete my e-mail id from that particular cell, its should give him warning with Yes (continue) \ No (undo) option, and if he still choose to continue, as a consequenses, it should delete all the content of the sheet table and automatically save the sheet.

I know I can restrict any body from deleting my e-mail id by protecting that area with password, but the fact is worksheet is already protected, but I can't set password, because,
there is some prioritization script I have written for worksheet "WIP" on "on calculation" event, also I am developing another macro which will take some range of data to next sheet, for all these script to run I have set activesheet.unprotect command, so password has to be blanck or else script will not run

still if you have any queries, please let me know
0
one more thing ...
on change event, even if it triggered on hitting enter button, it will do, as long as key cell remains unchanges .. it should do nothing & let other script \ macro run as set

However, if "on calculation" even or clicking button command also triggers "on Change" event, then there may be conflict, so in that case, these script should run first & then the other script as sheduled ...
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 11, 2010 at 05:22 AM
I am a bit confused over protection. All cells seems to be unprotected in WIP. So what is being protected ?

Also you can have a sheet protected with password too. When you unprotect, you need to give password too. like
Sheets("sheetname").UnProtect password:="password"


You can do this
have a module level variable

Dim myEmail As String


On Sheet Activate event

add one line

if myEmail = "" then myEmail = Range("M12")


In you on calculate event, before you do any thing, check value of M12

    If (myEmail <> Range("M12")) Then
        vConfirm = MsgBox("Email ID is not correct. Warning if you continue, bad things are going to happen", vbOKCancel + vbDefaultButton2 + vbCritical)
    
        If (vbConfirm = vbOK) Then
            Application.EnableEvents = False
            Range("A2:L" & Rows.Count).Clear
            ActiveWorkbook.Save
        Else
            Exit Sub
        End If
    End If
0
Hi Riz,

Thanks for codes, but it seem my problem got resolved with below,
Sheets("sheetname").UnProtect password:="password"

just one small doubt, if suppos, after running codes, i need to again reinstat passward protection will below script work?

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowInsertingHyperlinks:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveSheet.Protect Password:="password"

right now I am testing with it & it seem working fine, but if you still see any conflict, you can let me know

thnx very much!!!
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
770
Jun 11, 2010 at 08:02 AM
To be honest with you, I have never used protection on sheet. But all looks good to me and your test seems to confirm same.

However you can do this in one step

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowInsertingHyperlinks:=True, AllowFiltering:=True, _
AllowUsingPivotTables:=True, Password:="password"
0