using Dirty Method
greetings
I have a range of cells (C5:H7) where users can enter data. But, if they go back to change a previously entered value, I'd like a msg box to say "Are you sure you want to change this value?". I suspect the Dirty Method is involved, but I can't get the syntax to work. Any clues? Cheers, Giselle |
using Dirty Method
Hi Giselle:
Try: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim res As Long Dim oldVal As Variant Dim newVal As Variant Dim sAdd As String If Target.Count 1 Then Exit Sub On Error GoTo XIT With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Intersect(Me.Range("C5:H7"), Target) If Not rng Is Nothing Then sAdd = ActiveCell.Address newVal = rng.Value Application.Undo oldVal = rng.Value With rng If Not IsEmpty(.Value) Then res = MsgBox( _ Prompt:="Are you sure you want " & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbYesNo) If res = vbNo Then .Value = oldVal Else .Value = newVal End If End If End With End If Me.Range(sAdd).Activate XIT: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub '<<============= This is worksheet event code and should be pasted into the worksheets's code module (not a standard module and not the workbook's ThisWorkbook module): Right-click the worksheet's tab Select 'View Code' from the menu and paste the code. Alt-F11 to return to Excel. --- Regards, Norman "Giselle" wrote in message ... greetings I have a range of cells (C5:H7) where users can enter data. But, if they go back to change a previously entered value, I'd like a msg box to say "Are you sure you want to change this value?". I suspect the Dirty Method is involved, but I can't get the syntax to work. Any clues? Cheers, Giselle |
using Dirty Method
thank you Norman
Giselle "Norman Jones" wrote in message ... Hi Giselle: Try: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim res As Long Dim oldVal As Variant Dim newVal As Variant Dim sAdd As String If Target.Count 1 Then Exit Sub On Error GoTo XIT With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Intersect(Me.Range("C5:H7"), Target) If Not rng Is Nothing Then sAdd = ActiveCell.Address newVal = rng.Value Application.Undo oldVal = rng.Value With rng If Not IsEmpty(.Value) Then res = MsgBox( _ Prompt:="Are you sure you want " & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbYesNo) If res = vbNo Then .Value = oldVal Else .Value = newVal End If End If End With End If Me.Range(sAdd).Activate XIT: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub '<<============= This is worksheet event code and should be pasted into the worksheets's code module (not a standard module and not the workbook's ThisWorkbook module): Right-click the worksheet's tab Select 'View Code' from the menu and paste the code. Alt-F11 to return to Excel. --- Regards, Norman "Giselle" wrote in message ... greetings I have a range of cells (C5:H7) where users can enter data. But, if they go back to change a previously entered value, I'd like a msg box to say "Are you sure you want to change this value?". I suspect the Dirty Method is involved, but I can't get the syntax to work. Any clues? Cheers, Giselle |
using Dirty Method
Greetings Norman & others
This code works perfectly at asking for confirmation before EXISTING values in the range("C5:H7") are changed. But, I need to allow users to make initial entries first, asking for confirmation if they then try to change a value. (This usually happens when the users are quickly trying to enter data without realizing they are typing over data they have already entered.) Any ideas? Thanks Giselle "Norman Jones" wrote in message ... Hi Giselle: Try: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim res As Long Dim oldVal As Variant Dim newVal As Variant Dim sAdd As String If Target.Count 1 Then Exit Sub On Error GoTo XIT With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Intersect(Me.Range("C5:H7"), Target) If Not rng Is Nothing Then sAdd = ActiveCell.Address newVal = rng.Value Application.Undo oldVal = rng.Value With rng If Not IsEmpty(.Value) Then res = MsgBox( _ Prompt:="Are you sure you want " & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbYesNo) If res = vbNo Then .Value = oldVal Else .Value = newVal End If End If End With End If Me.Range(sAdd).Activate XIT: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub '<<============= This is worksheet event code and should be pasted into the worksheets's code module (not a standard module and not the workbook's ThisWorkbook module): Right-click the worksheet's tab Select 'View Code' from the menu and paste the code. Alt-F11 to return to Excel. --- Regards, Norman "Giselle" wrote in message ... greetings I have a range of cells (C5:H7) where users can enter data. But, if they go back to change a previously entered value, I'd like a msg box to say "Are you sure you want to change this value?". I suspect the Dirty Method is involved, but I can't get the syntax to work. Any clues? Cheers, Giselle |
using Dirty Method
Hi Giselle.
To correct a cell selection problem, to handle possible formula entries, and to handle the replacement of a cell entry with an identical entry (i.e effectively no change), try the following revision: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim res As Long Dim oldVal As Variant Dim newVal As Variant Dim sAdd As String If Target.Count 1 Then Exit Sub On Error GoTo XIT With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Intersect(Me.Range("C5:H7"), Target) If Not rng Is Nothing Then sAdd = ActiveCell.Address newVal = Target.Formula Application.Undo oldVal = rng.Formula With rng If Not IsEmpty(.Value) And newVal < oldVal Then res = MsgBox( _ Prompt:="Are you sure you want " & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbYesNo) If res = vbNo Then .Formula = oldVal Else .Formula = newVal End If Else .Formula = newVal End If End With End If If Not res = vbNo Then Me.Range(sAdd).Activate XIT: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub '<<============= But, I need to allow users to make initial entries first, asking for confirmation if they then try to change a value. I believe that the code does this: an entry in an empty cell engenders no response; a replacement with an identical entry engenders no response. Any change in value/formula in the specified range invokes the message box. Depending on the response to the message box, the new entry is retained or the previous entry is restored. In the latter case, the cursor is returned to the cell with the aborted entry, --- Regards, Norman "Giselle" wrote in message ... Greetings Norman & others This code works perfectly at asking for confirmation before EXISTING values in the range("C5:H7") are changed. But, I need to allow users to make initial entries first, asking for confirmation if they then try to change a value. (This usually happens when the users are quickly trying to enter data without realizing they are typing over data they have already entered.) Any ideas? Thanks Giselle "Norman Jones" wrote in message ... Hi Giselle: Try: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim res As Long Dim oldVal As Variant Dim newVal As Variant Dim sAdd As String If Target.Count 1 Then Exit Sub On Error GoTo XIT With Application .EnableEvents = False .ScreenUpdating = False End With Set rng = Intersect(Me.Range("C5:H7"), Target) If Not rng Is Nothing Then sAdd = ActiveCell.Address newVal = rng.Value Application.Undo oldVal = rng.Value With rng If Not IsEmpty(.Value) Then res = MsgBox( _ Prompt:="Are you sure you want " & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbYesNo) If res = vbNo Then .Value = oldVal Else .Value = newVal End If End If End With End If Me.Range(sAdd).Activate XIT: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub '<<============= This is worksheet event code and should be pasted into the worksheets's code module (not a standard module and not the workbook's ThisWorkbook module): Right-click the worksheet's tab Select 'View Code' from the menu and paste the code. Alt-F11 to return to Excel. --- Regards, Norman "Giselle" wrote in message ... greetings I have a range of cells (C5:H7) where users can enter data. But, if they go back to change a previously entered value, I'd like a msg box to say "Are you sure you want to change this value?". I suspect the Dirty Method is involved, but I can't get the syntax to work. Any clues? Cheers, Giselle |
using Dirty Method
That was nifty Norman. Davidm -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=508707 |
using Dirty Method
Hello, I really benifited from this example! It is really close to what I need to do...but I have two quick add on questions.. 1. If the cell change is accepted, how to I automatically change the text to BOLD and RED?? 2. Even if there is no previous change, I would like any change to any cell in the range indicted with a change in text font and color. Any thoughts? Thanks, Matt -- matt4003 ------------------------------------------------------------------------ matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635 View this thread: http://www.excelforum.com/showthread...hreadid=508707 |
using Dirty Method
very COOL
Giselle |
using Dirty Method
Hi Matt,
Try inserting the additional code lines: With Target .Font.Bold = True .Interior.ColorIndex = 3 End With after: If Not res = vbNo Then Me.Range(sAdd).Activate --- Regards, Norman "matt4003" wrote in message ... Hello, I really benifited from this example! It is really close to what I need to do...but I have two quick add on questions.. 1. If the cell change is accepted, how to I automatically change the text to BOLD and RED?? 2. Even if there is no previous change, I would like any change to any cell in the range indicted with a change in text font and color. Any thoughts? Thanks, Matt -- matt4003 ------------------------------------------------------------------------ matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635 View this thread: http://www.excelforum.com/showthread...hreadid=508707 |
using Dirty Method
Norman, This is great!! I am really learning a lot and it is perfect for m project. Can I ask another question, because I think you will be abl to answer it without thinking:-) I have four rows of input for each entry (typically there are 50-10 entries per FCST): 1. Potential Sales 2. Sales FCST 3. Shipping FCST 4. Original Booked I would like a message box to appear (similar to the one in the earlie code) that says you "Can't change Original Booked". This is becaus this field is pre-populated and is for reference. I would imagine the statement to be: If range(same row, column B) = "Original Booked" Then res = MsgBox( _ Prompt:="You can't change Original Booked" & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbCancel) If res = Cancel Then .Formula = oldVal End If End With Any ideas?? Thanks, Mat -- matt400 ----------------------------------------------------------------------- matt4003's Profile: http://www.excelforum.com/member.php...nfo&userid=963 View this thread: http://www.excelforum.com/showthread.php?threadid=50870 |
using Dirty Method
Anyone have any ideas, I am trying to close out my project today, and am just stuck:-( Norman, This is great!! I am really learning a lot and it is perfect for my project. Can I ask another question, because I think you will be able to answer it without thinking:-) I have four rows of input for each entry (typically there are 50-100 entries per FCST): 1. Potential Sales 2. Sales FCST 3. Shipping FCST 4. Original Booked I would like a message box to appear (similar to the one in the earlier code) that says you "Can't change Original Booked". This is because this field is pre-populated and is for reference. I would imagine the statement to be: If range(same row, column B) = "Original Booked" Then res = MsgBox( _ Prompt:="You can't change Original Booked" & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbCancel) If res = Cancel Then ..Formula = oldVal End If End With Any ideas?? Thanks, Matt -- matt4003 ------------------------------------------------------------------------ matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635 View this thread: http://www.excelforum.com/showthread...hreadid=508707 |
using Dirty Method
Hi Matt,
Try something like: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rng2 As Range Dim res As Long Dim oldVal As Variant Dim newVal As Variant Dim sAdd As String Dim msg As String msg = "You can't change Original Booked " & _ "to change the value of Cell " If Target.Count 1 Then Exit Sub Set rng = Intersect(Me.Range("C5:H7"), Target) Set rng2 = Intersect(Me.Columns("A"), Target) On Error GoTo XIT With Application .EnableEvents = False .ScreenUpdating = False End With If Not rng Is Nothing Then sAdd = ActiveCell.Address newVal = Target.Formula Application.Undo oldVal = rng.Formula With rng If Not IsEmpty(.Value) And newVal < oldVal Then res = MsgBox( _ Prompt:="Are you sure you want " & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbYesNo) If res = vbNo Then .Formula = oldVal Else .Formula = newVal End If Else .Formula = newVal End If End With If Not res = vbNo Then Me.Range(sAdd).Activate With Target .Font.Bold = True .Interior.ColorIndex = 3 End With End If If Not rng2 Is Nothing Then With rng2 If LCase(.Offset(0, 1).Value) = "original booked" Then Application.Undo MsgBox Prompt:=msg & Target.Address(0, 0), _ Buttons:=vbCritical, _ Title:="Locked Field" End If End With End If XIT: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub '<<============= This assumes that the headings are in column B and the data is entered in column A. --- Regards, Norman "matt4003" wrote in message ... Anyone have any ideas, I am trying to close out my project today, and am just stuck:-( Norman, This is great!! I am really learning a lot and it is perfect for my project. Can I ask another question, because I think you will be able to answer it without thinking:-) I have four rows of input for each entry (typically there are 50-100 entries per FCST): 1. Potential Sales 2. Sales FCST 3. Shipping FCST 4. Original Booked I would like a message box to appear (similar to the one in the earlier code) that says you "Can't change Original Booked". This is because this field is pre-populated and is for reference. I would imagine the statement to be: If range(same row, column B) = "Original Booked" Then res = MsgBox( _ Prompt:="You can't change Original Booked" & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbCancel) If res = Cancel Then Formula = oldVal End If End With Any ideas?? Thanks, Matt -- matt4003 ------------------------------------------------------------------------ matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635 View this thread: http://www.excelforum.com/showthread...hreadid=508707 |
using Dirty Method
Hello Norman, Thanks for the help. Unfortunately, I am using a range of cells (F92:Q235) Yes, column "B" is where the "Orig. Booked Quantity" statement is. How do I make the reference more dynamic? Thanks, Matt -- matt4003 ------------------------------------------------------------------------ matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635 View this thread: http://www.excelforum.com/showthread...hreadid=508707 |
using Dirty Method
Hi Matt,
Try: '============= Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rng2 As Range Dim res As Long Dim oldVal As Variant Dim newVal As Variant Dim sAdd As String Dim msg As String msg = "You can't change Original Booked " & _ "to change the value of Cell " If Target.Count 1 Then Exit Sub Set rng = Intersect(Me.Range("C5:H7"), Target) Set rng2 = Intersect(Me.Range("F92:Q235"), Target) On Error GoTo XIT With Application .EnableEvents = False .ScreenUpdating = False End With If Not rng Is Nothing Then sAdd = ActiveCell.Address newVal = Target.Formula Application.Undo oldVal = rng.Formula With rng If Not IsEmpty(.Value) And newVal < oldVal Then res = MsgBox( _ Prompt:="Are you sure you want " & _ "to change the value of " & _ "Cell " & rng.Address(0, 0) & "?", _ Buttons:=vbYesNo) If res = vbNo Then .Formula = oldVal Else .Formula = newVal End If Else .Formula = newVal End If End With If Not res = vbNo Then Me.Range(sAdd).Activate With Target .Font.Bold = True .Interior.ColorIndex = 3 End With End If If Not rng2 Is Nothing Then With rng2 If LCase(Cells(.Row, "B").Value) = "original booked" Then Application.Undo MsgBox Prompt:=msg & Target.Address(0, 0), _ Buttons:=vbCritical, _ Title:="Locked Field" End If End With End If XIT: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub '<<============= -- --- Regards, Norman "matt4003" wrote in message ... Hello Norman, Thanks for the help. Unfortunately, I am using a range of cells (F92:Q235) Yes, column "B" is where the "Orig. Booked Quantity" statement is. How do I make the reference more dynamic? Thanks, Matt -- matt4003 ------------------------------------------------------------------------ matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635 View this thread: http://www.excelforum.com/showthread...hreadid=508707 |
using Dirty Method
Hello Norman, Thanks for your quick response. Still fighting this. My data falls in F92:Q235 "Original Booked" indicator is in column B. So how do I change the rng to match this?? Is this the only thing that needs to be changed so that if I try to add or change a value in the row which contains "Original Booked" in column "B", it would give me a msg saying NO... Set rng = Intersect(Me.Range("C5:H7"), Target) Set rng2 = Intersect(Me.Range("F92:Q235"), Target) Thanks, Matt -- matt4003 ------------------------------------------------------------------------ matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635 View this thread: http://www.excelforum.com/showthread...hreadid=508707 |
using Dirty Method
Hi Matt,
so that if I try to add or change a value in the row which contains "Original Booked" in column "B", it would give me a msg saying NO... In my tests, the code included in my last post does exactly that - with the more detailed message previously proposed by you. --- Regards, Norman "matt4003" wrote in message ... Hello Norman, Thanks for your quick response. Still fighting this. My data falls in F92:Q235 "Original Booked" indicator is in column B. So how do I change the rng to match this?? Is this the only thing that needs to be changed so that if I try to add or change a value in the row which contains "Original Booked" in column "B", it would give me a msg saying NO... Set rng = Intersect(Me.Range("C5:H7"), Target) Set rng2 = Intersect(Me.Range("F92:Q235"), Target) Thanks, Matt -- matt4003 ------------------------------------------------------------------------ matt4003's Profile: http://www.excelforum.com/member.php...fo&userid=9635 View this thread: http://www.excelforum.com/showthread...hreadid=508707 |
All times are GMT +1. The time now is 07:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com