I am trying to use VBA to do the following:

if there's a change to the worksheet,
if a cell is green
don't allow the change; go back to the original
value before the cell was changed.
else (cell is not green)
allo change

my problem is the "go back to the original value before
the cell was changed." any ideas how i might go about this?

i would just lock the fields and protect the sheet, but
the workbook is shared and i don't want to protect the
sheet in the shared workbook because then i can't insert
rows without unprotecting the workbook, etc.

thanks.

Mika,

Here you go

Dim myVar

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
Application.EnableEvents = False
On Error GoTo ws_exit
If Target.Interior.ColorIndex = 10 Then
Target.Value = myVar
MsgBox "Change cancelled", vbInformation
End If
ws_exit:
Application.EnableEvents = True
End If
End Sub

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


--

HTH

Bob Phillips

"mika." wrote in message
...
I am trying to use VBA to do the following:

if there's a change to the worksheet,
if a cell is green
don't allow the change; go back to the original
value before the cell was changed.
else (cell is not green)
allo change

my problem is the "go back to the original value before
the cell was changed." any ideas how i might go about this?

i would just lock the fields and protect the sheet, but
the workbook is shared and i don't want to protect the
sheet in the shared workbook because then i can't insert
rows without unprotecting the workbook, etc.

thanks.

Hi,



First, in the even Selection _Change store what in the target cell in a
global variable. Then, if they could not change the cell put it back.





Private varBefore As Variant



Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyCell As Range

For Each MyCell In Selection

If MyCell.Interior.ColorIndex = 35 Then

MyCell.Value = varBefore

MsgBox "You can't change this fields"

End If

Next

GoTo ending



End Sub



Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.Value = varBefore

End Sub






--
JP

http://www.solutionsvba.com


"mika." wrote in message
...
Hi John,

I don't think I understand what you said: are you advising
me to take what I have in my worksheet, copy it into
another sheet, and then if the change should not be made,
use the other sheet to get the right value back in the
cell?

This is what I have so far:

Private Sub Worksheet_Change(ByVal Target As Range)

For Each cell In Selection
If cell.Interior.ColorIndex = 35 Then
GoTo error
End If
Next
GoTo ending

error: MsgBox ("You cannot change these fields
'Code to not change the fields

ending:
End If

...Could you please explain what you might put in
the 'Code to not change the fields part?

Thanks so much.



-----Original Message-----
mika,

1) Store the previous value [and possibly cell address]
as a named range, or
2) If this is too insecure, use a Hidden Sheet.

Use the following Event to store the values away at the
end of
SelectionChange, ready to be restored if you need them.

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)

End Sub

regards,

JohnI


"mika." wrote in message
...
I am trying to use VBA to do the following:

if there's a change to the worksheet,
if a cell is green
don't allow the change; go back to the original
value before the cell was changed.
else (cell is not green)
allo change

my problem is the "go back to the original value before
the cell was changed." any ideas how i might go about
this?

i would just lock the fields and protect the sheet, but
the workbook is shared and i don't want to protect the
sheet in the shared workbook because then i can't insert
rows without unprotecting the workbook, etc.

thanks.


.