View Full Version : Solved: msg box then deleting field when cell changed
Rob342
12-17-2010, 09:22 AM
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
12-17-2010, 10:17 AM
You could do it with VBA if that is acceptable.
Rob342
12-17-2010, 02:21 PM
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
12-17-2010, 03:05 PM
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
12-18-2010, 08:34 AM
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
12-18-2010, 09:41 AM
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
12-18-2010, 11:09 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.