Help with protecting cells
 

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

--
Prometheu
-----------------------------------------------------------------------
Prometheus's Profile: http://www.excelforum.com/member.php...fo&userid=1569
View this thread: http://www.excelforum.com/showthread.php?threadid=47339

Help with protecting cells
 

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.

--
Regards,
Tom Ogilvy

"Prometheus" wrote
in message ...

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 the
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 while
entering data, frmChange pops up every time they come to a protected
cell. My question is, is it possible to only invoke the form when the
user tries to enter data into the cell instead of when the cell is
selected.


--
Prometheus
------------------------------------------------------------------------
Prometheus's Profile:
http://www.excelforum.com/member.php...o&userid=15697
View this thread: http://www.excelforum.com/showthread...hreadid=473397

Help with protecting cells
 

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?


--
Prometheus
------------------------------------------------------------------------
Prometheus's Profile: http://www.excelforum.com/member.php...o&userid=15697
View this thread: http://www.excelforum.com/showthread...hreadid=473397

Help with protecting cells
 

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

--
Regards,
Tom Ogilvy


"Prometheus" wrote
in message ...

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?


--
Prometheus
------------------------------------------------------------------------
Prometheus's Profile:
http://www.excelforum.com/member.php...o&userid=15697
View this thread: http://www.excelforum.com/showthread...hreadid=473397

Help with protecting cells
 

Application.undo for the win, thanks :)


--
Prometheus
------------------------------------------------------------------------
Prometheus's Profile: http://www.excelforum.com/member.php...o&userid=15697
View this thread: http://www.excelforum.com/showthread...hreadid=473397