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" <matt4003.22tppm_1139255702.2637@excelforum-nospam.com> wrote in
message news:matt4003.22tppm_1139255702.2637@excelforum-nospam.com...
>
> 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
>

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

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" <matt4003.22tzny_1139268602.78@excelforum-nospam.com> wrote in
message news:matt4003.22tzny_1139268602.78@excelforum-nospam.com...
>
> 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
>

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

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" <matt4003.22u4an_1139274602.7138@excelforum-nospam.com> wrote in
message news:matt4003.22u4an_1139274602.7138@excelforum-nospam.com...
>
> 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
>