Problem with calculating

[Prometheus]

I have a spreadsheet in which I'm trying to prevent users fro
accidentally modifying cells containing formulas. On occasion
however, they will need to manually input data instead of allowing th
formula to calculate. Using info found on this forum I put

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "D9:O9"
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Application.ScreenUpdating = False
frmChange.Show
Application.ScreenUpdating = True
End If
End Sub

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

into the worksheet. frmChange.Show asks the user if s/he would like t
change the contents of the cell and contains

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

Private Sub cmdNo_Click()
ActiveCell.Offset(1, 0).Select
Unload Me
End Sub

Private Sub cmdYes_Click()
ActiveCell.ClearContents
Unload Me
End Sub

Code

 

[Prometheus]

I saw a reference to setting xcalculation to semiautomatic but I can'
seem to get that to work, any suggestions

 

[Dave Peterson]

Are you asking if you can stop the worksheet from recalculating when you
clearcontents of the cell?

You could turn calculation off, clear the contents, then turn calculation back
on when the user selects another cell--but that sounds dangerous to me.

I guess my question is why bother clearing the cell at all. Just let them type
the new entry.

=====
Another option I've used when I want to give users a chance to override formulas
is to use multiple cells.

Say I a formula in C12.
I'd use D12 as the User Input Cell
Then I'd use this is E12:
=if(d12="",c12,d12)

And have every subsequent formulas point at E12 (since that contains the results
of the formula or the user override).

 

[Prometheus]

Excellent, thanks for the tip. I thought it would be nice to clear the
cell for the user but didn't realize that was causing the
recalculation. One other question I have is if it's possible to
declare more than one range in the code

Code:
--------------------

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Const WS_RANGE As String = "D9:O9"
On Error Resume Next
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Application.ScreenUpdating = False
frmChange.Show
End If

End Sub

 

[Dave Peterson]

Const WS_RANGE As String = "D9:O9,"X1:y2"

would be one way.

 

[Dave Peterson]

And if that increment were nice, you could even use some arithmetic:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Me.Range("d")) Is Nothing Then Exit Sub

If Target.Row < 9 Then Exit Sub
If Target.Row Mod 4 <> 1 Then Exit Sub

Application.ScreenUpdating = False
frmChange.Show
Application.ScreenUpdating = True

End Sub

This got row 9, 13, 17, 21, ...

(I didn't see a pattern with your sample.)

 

[Prometheus]

Well if there's a mathematical formula to determine the location of th
rows I need to protect I'm not smart enough to figure it out. However
the first example was just what I needed, thanks