Delete entire content if cell A1 value change [Solved/Closed]

Report
-
 Nitin -
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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.
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 ...
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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
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!!!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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"
Thanks Riz .. it resolved