Display MsgBox whenever cell is deleted / changed to empty

  • Hi guys,


    I am trying to write down simple VBA code that returns a MsgBox with a warning whenever at least one of two cells is deleted / changed to empty.


    Kindest regards,
    Mariana

  • Re: Display MsgBox whenever cell is deleted / changed to empty


    After some online research I tried something like this but it is not running automatically.. if i execute the code it does work :s

  • Re: Display MsgBox whenever cell is deleted / changed to empty


    Hi and welcome to Ozgrid.


    Please have a read of the forum rules and learn to use code tags, which are required anytime you post VBA code.


  • Re: Display MsgBox whenever cell is deleted / changed to empty


    Hi, thanks! Sorry, will have a read asap..


    It is still not working though. I press "Delete" on either one of cells C6, C7 and nothing happens. Any idea why?

  • Re: Display MsgBox whenever cell is deleted / changed to empty


    Did you put the code into the correct worksheet module? Right-click the tab of the target worksheet and choose view code to open the VB Editor with the worksheet module already in view.
    If using Excel 2007+, have you saved the workbook as a .xlsm file type ( as opposed to .xlsx)?


    The code works for me.

  • Re: Display MsgBox whenever cell is deleted / changed to empty


    Yes I did.. just repeated the process as per your advice just to make sure i had done it the right way but it still doesn't work. The file is saves as .xslm type


    Is it possible that it doesn't recognize deleting the value of a cell as a change?

  • Re: Display MsgBox whenever cell is deleted / changed to empty


    It's working already, thanks!
    My bad, I had forgotten some old code uncommented at the bottom


    Thank you so much :)

  • Re: Display MsgBox whenever cell is deleted / changed to empty


    One more cry for help.


    I am now trying to add a msgbox so that the user confirms he/she wants to change the field value and if he answers "No" I want the cell to go back to its original value. I repeated the code for empty cell but this time <> meaning that he changed the cell value for something different than empty.
    Whenever i press the "No" button, i get stuck at it and the msgbox keeps popping up.


    Any ideas?


    Tks!


    Code
    If Not Intersect(Target, Range("C6:C7")) Is Nothing Then
            If Target.Value <> "" Then
                Msg = "Are you sure you wnat to change this field?"
                Ans = MsgBox(Msg, vbYesNo)
                If Ans <> vbYes Then
                    Application.Undo
                End If
             End If
        End If
  • Re: Display MsgBox whenever cell is deleted / changed to empty


    You should always declare your variables and never code without using Option Explicit as it will force you to do so.



    Excel VBA Variables
    The Scope and Lifetime of Excel VBA Variables
    Chip Pearson - Declaring Variable / Option Explicit

  • Re: Display MsgBox whenever cell is deleted / changed to empty


    Hi AAE, thanks for the tip! I am a complete newby to this.


    It is still getting stuck whenever i press the "No" answer. It changes the cell value to its original value but then it triggers a new msg box (perhaps application.undo is also being recognized as changing event?)

  • Re: Display MsgBox whenever cell is deleted / changed to empty


    The code works perfectly for me. You need to upload all of the code in the sheet module so we have full context.
    Better yet, upload a sample workbook (dummy data) with the code.

  • Re: Display MsgBox whenever cell is deleted / changed to empty


    Oh and now if i delete the cell content it goes back to the original value and it also pops up the msg box.


  • Re: Display MsgBox whenever cell is deleted / changed to empty


    You have two sets of conflicting code. In the first set, you test for the target cell equal to a blank and in the second the test is for *NOT* equal to a blank.


    The second set of code seems to be what you asked for so delete the first code set and in the second set change the <> operator to =.
    Revise your message box accordingly.


    Else, explain clearly why you need to different comparisons and be clear about what your actual needs are.

Participate now!

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