Problem with calculating

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 wrote:
>
> I have a spreadsheet in which I'm trying to prevent users from
> accidentally modifying cells containing formulas. On occasion,
> however, they will need to manually input data instead of allowing the
> 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 to
> 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:
> --------------------
>
> This works perfectly for me accept that if the user clicks 'Yes' then the entire workbook recalculates. Having had other recalculation problems in the past, is there a way to prevent the recalculation of the entire worksheet? Basically what I'm looking for is a way to get the same effect as setting Calculation to Manual, except that during the course of normal data entry I need the cells containing formulas to calculate.
>
> --
> Prometheus
> ------------------------------------------------------------------------
> Prometheus's Profile: http://www.excelforum.com/member.php...o&userid=15697
> View this thread: http://www.excelforum.com/showthread...hreadid=470342

--

Dave Peterson

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

would be one way.

Prometheus wrote:
>
> 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
>
> --------------------
>
> For example, is there a way to set the range as D9:09, D13:013,
> D15:015, etc, without creating a new sub for each?
>
> --
> Prometheus
> ------------------------------------------------------------------------
> Prometheus's Profile: http://www.excelforum.com/member.php...o&userid=15697
> View this thread: http://www.excelforum.com/showthread...hreadid=470342

--

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:o")) 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 wrote:
>
> 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
>
> --------------------
>
> For example, is there a way to set the range as D9:09, D13:013,
> D15:015, etc, without creating a new sub for each?
>
> --
> Prometheus
> ------------------------------------------------------------------------
> Prometheus's Profile: http://www.excelforum.com/member.php...o&userid=15697
> View this thread: http://www.excelforum.com/showthread...hreadid=470342

--

Dave Peterson

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