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
-------------------

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

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

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

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

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

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

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 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

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