Solved: msg box then deleting field when cell changed

[Rob342]

Hi All

Can any help me with this problem
When a user enters a value from a drop down box in cell B4, then selects a value from a drop down box in D17, then goes back and changes the value in cell B4.
What i would like is a message box for the user "You are changing the value" if they then accept no dont change the value of B4, if they say yes to the prompt then delete the value in D17.

I have tried various If's and then ststements but it dos not work, one of the problems seem to be the drop down box & select change activates before the actual value changes.

Can anybody help with a suggestion please ?
I have attached a test copy for info

[Bob Phillips]

You could do it with VBA if that is acceptable.

[Rob342]

Hi XLD,

VBA would be great, i have tried all sort of equations in vba but to no avail, could you post the correct way to do it. i'm still climbing the mountain to learn vba.
Many thanks for you support
Regards

[Bob Phillips]

Option Explicit

Private PrevVal As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If Not IsEmpty(PrevVal) Then

If MsgBox("Do you want to change this value (Y/N)?", vbYesNo, "Project Data") = vbYes Then

Me.Range("A17").Value2 = ""
PrevVal = Target.Value
Else

Target.Value = PrevVal
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PrevVal = Target.Value
End Sub


This is worksheet event code.
To implement it, select the sheet tab, right click, and
select View Code.
Paste this code into the code module that opens in the
VBIDE.
Then close the VBIDE and test it in Excel.

[Rob342]

Hi XLD

Have pasted the code works a treat.
Two questions if the field in B4 =" " and A17 = " " is there a way to stop the message box from appearing ie opening of the sheet for the 1st time where both field would be blank and the other sencerio is if B4 <> " " and A17 = " " can i stop the msg box from appearing, is that possible?

Many thanks for your time most appreciated.

Where would we be without the VBAX Forumn ?
Regards

[Bob Phillips]

Does this do what you want?



Option Explicit

Private PrevVal As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4" '<<<< change to suit

On Error GoTo ws_exit

Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

If Not IsEmpty(PrevVal) And Me.Range("A17").Value2 <> "" Then

If MsgBox("Do you want to change this value (Y/N)?", vbYesNo, "Project Data") = vbYes Then

Me.Range("A17").Value2 = ""
PrevVal = Target.Value
Else

Target.Value = PrevVal
End If
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PrevVal = Target.Value
End Sub

[Rob342]

Hi XLD

Does exactly what its supposed to do.

Thankyou for your time & help
Have a good xmas
Regards

PS i'll mark it as solved ok