Use checkbox to toggle between formula and manual input

[packat]

I have an array of formulas.
I wanted to enable manual user input on these array but
still preserve the formula. So I added a checkbox.
(DefaultOpt).

If the box is checked the formula is restored into the
cells in the array. The following code seems to work. (But
suggestion for any improvement is welcome. I am sort of
green at Excel/VBA)

----
Private Sub defaultOpt_Click()
For i = 15 To 28
ActiveWorkbook.Worksheets("Model 2").Cells(i,
4).Formula = "=FORMULA HERE"
end sub
----

However, I also want to make sure that, if the value of a
cell in the array is changed, the box should become
unchecked.

Any suggestion how I proceed to do this?
Thanks in advance,
pac

 

[Bernie Deitrick]

pac,

In the worksheet's codemodule, use this:

Private Sub defaultOpt_Click()
ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize(14, 1).Formula = _
"=Actual FORMULA HERE, written for cell D15"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then
Sheet1.defaultOpt.Value = False
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[Guest]

-----Original Message-----
pac,

In the worksheet's codemodule, use this:

Private Sub defaultOpt_Click()
ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize (14, 1).Formula = _
"=Actual FORMULA HERE, written for cell D15"
End Sub

Thanks for the response. It works to some extent. Still
some problem (design problem really). When the check box
is clicked, the formula is reinstated, and as a result,
some of the values oif the cells in the range was update.
This causes a collision between the two Sub's.

I need to add another if condition in the second Sub to
avoid changes made by the formula itself. Any diea?

Thanks much,
pac

 

[Bernie Deitrick]

pac,

Thanks for the response. It works to some extent. Still
some problem (design problem really). When the check box
is clicked, the formula is reinstated

Isn't that what you want?

and as a result,
some of the values oif the cells in the range was update.
This causes a collision between the two Sub's.

I need to add another if condition in the second Sub to
avoid changes made by the formula itself.

Any diea?

Not really. You'll need to post a very clear example of what you mean.

Bernie

 

[Guest]

-----Original Message-----
pac,

Isn't that what you want?

Sorry for being unclear. The problem is when I check the
check box, first defaultOpt kicks in and change the cell
back to formula. But...

The result of this change will trigger the
Worksheet_Change procedure, which switch the checkbox back
to uncheck.

The question is, how do we modify Worksheet_Change to
ignore the change made by defaultOpt procedure.

Thanks,
pac

P.S> I paste the two procedures back for your convenience.

---------------------
Private Sub defaultOpt_Click()
ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize
(14, 1).Formula = _
"=Actual FORMULA HERE, written for cell D15"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is
Nothing Then
Sheet1.defaultOpt.Value = False
End If
End Sub

 

[Guest]

The question is, how do we modify Worksheet_Change to

ignore the change made by defaultOpt procedure.

Thanks,
pac

P.S> I paste the two procedures back for your convenience.

---------------------
Private Sub defaultOpt_Click()
ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize
(14, 1).Formula = _
"=Actual FORMULA HERE, written for cell D15"
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is
Nothing Then
Sheet1.defaultOpt.Value = False
End If
End Sub
-----------------

I guess I wanted to add a check statement if the content
of the cell is the same as the formula. But I am not sure
if the syntax is correct.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is
Nothing Then



Sheet1.defaultOpt.Value = False
End If
End Sub

 

[Bernie Deitrick]

pac,

I thought that was taken care of by the cells.count>1 line...

Anyway, use

Application.EnableEvents = False
'other code
Application.EnableEvents = True

Private Sub defaultOpt_Click()

Application.EnableEvents = False
ActiveWorkbook.Worksheets("Model 2").Cells(15, 4).Resize(14, 1).Formula = _
"=Actual FORMULA HERE, written for cell D15"
Application.EnableEvents = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Cells(15, 4).Resize(14, 1)) Is Nothing Then

Application.EnableEvents = False
Sheet1.defaultOpt.Value = False

Application.EnableEvents = True
End If
End Sub

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

pac,

This requires the use of undo: I will post the code tomorrow when I have
time.

Bernie

 

[Dave Peterson]

This may not work for you.

But I use a couple of columns.

Say column A contains the question/description/whatever.
Then column B could contain the formula (call it the calculated default answer)
Column C would contain the manual override value
Then Column D would contain the value to be used.

=if(c2<>"",c2,b2)

And all subsequent formulas would point at the values in column D.

If you find seeing column D irritating, you could hide it.

With the worksheet protected and the formula cells locked (and the manual input
columns unlocked), it may ever work!

 

[Guest]

-----Original Message-----
pac, ....

Anyway, use

Application.EnableEvents = False
'other code
Application.EnableEvents = True

Beautiful!

Thanks Bernie.