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
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
Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("C6:C7"))
If Not (isect Is Nothing) And Range("C6") = "" Then
MsgBox("This field cannot be blank.")
End If
If Not (isect Is Nothing) And Range("C7") = "" Then
MsgBox("This field cannot be blank.")
End If
End Sub
Display More
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!
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.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ans As Variant
Dim Msg As String
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
End Sub
Display More
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.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Ans As Variant
Dim Msg As String
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C6:C7")) Is Nothing Then
If Target.Value = "" Then
MsgBox "Por favor insira uma das duas opções da lista. Este campo não pode ficar em branco."
Application.Undo
End If
End If
If Not Intersect(Target, Range("C6:C7")) Is Nothing Then
If Target.Value <> "" Then
Msg = "Tem a certeza de que pretende alterar este campo?"
Ans = MsgBox(Msg, vbYesNo)
If Ans <> vbYes Then
Application.Undo
End If
End If
End If
End Sub
Display More
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.
Re: Display MsgBox whenever cell is deleted / changed to empty
anyone who can help on this? tks!
Don’t have an account yet? Register yourself now and be a part of our community!