Help with protecting cells

[Prometheus]

Right now I am protecting cells with

Code
-------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const WS_RANGE As String = "D7:O7,D9:O9,D13:O13,....."
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Application.ScreenUpdating = False
frmChange.Show
End If

End Sub

-------------------

The form asks the user if they would like to change the data in th
cell. If yes, it lets them. If no, it moves the focus down one row.
The problem is that as a user is moving down rows using <enter> whil
entering data, frmChange pops up every time they come to a protecte
cell. My question is, is it possible to only invoke the form when th
user tries to enter data into the cell instead of when the cell i
selected

 

[Tom Ogilvy]

change
SelectionChange
to
Change

would be the easy answer, but I suspect the code your not showing unprotects
the sheet. So I think your code will require a lot more work to manage
that.

 

[Prometheus]

That's almost exactly what I need. The only problem is it allows the
change in the cell before it loads the form. The cells contain
functions that should only be overwritten for special circumstances.
What I'm trying to do is load frmChange if the user tries to change the
cell. The user is asked if s/he wants to change the cell. If yes then
let the change occur, if not then leave the cell contents as they are.
With Worksheet_Change the change occurs and then the form is loaded, so
even if the user selects 'No', the change has already occured. Is
there any way around this?

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error goto Errhandler
Const WS_RANGE As String = "D7:O7,D9:O9,D13:O13,....."
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Application.ScreenUpdating = False
Application.EnableEvents = False
v = Target.Value
Application.Undo
ans = Msgbox("Allow Change?", vbYesNo)
if ans = vbYes then
Target.value = v
End if
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Prometheus]

Application.undo for the win, thanks