Preventing formula calculation under certain conditions...

[chazrab]

This formula

=(SUM(BE3:BE7)-SUM(BE8:BE26))

appears on row 30 of column A
As a value is added to a row between 8 and 26, the formula calculates - in this case, subtracts the value
from the numeric balance given by the formula. This works well enough.

But is it possible to add a value in any row from 8 to 26, have it appear in the cell, and NOT calculate the balance by subtracting the value ? This would be a conditional situation as I see it:

if Btn1 is pressed or clicked then
    =(SUM(BE3:BE7)-SUM(BE8:BE26)) = true (value is added to correct row in column - perform the calculation - subtraction)
Else (Btn2 is pressed or clicked)
   =(SUM(BE3:BE7)-SUM(BE8:BE26)) = false (value is added to correct row in column but do not calculate the result, but leave the formula intact)
End if

In case you have questions - bills are paid either out of a bank account(Btn1 code)
or, at times, with a credit card(Btn2)
Btn1 calculates the subtraction giving the real bank balance
Btn2 does NOT calculate, leaving the bank balance and formula unchanged, however
the credit card balance has that value ADDED to it.
All values are entered from a userform Textbox1 with two buttons, Btn1 and Btn2, giving the user the option of what source they want to pay a bill from.

The formula must remain intact in order to perform Btn1 calculations. The caveat is having a value
added in a column with a formula at the bottom, yet telling Excel not to perform the calculation.
My term for this is "conditional calculation" - and I don't know if it can be done. I tired to make this as simple and clear as possible without too much information.

Thanks for anyone's help.
cr

 

[veryamusing]

What about something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("C2").Value2 <> Evaluate("=SUM(C3:C7)-SUM(C8:C26)") Then
        Range("C2").Font.Color = vbRed
    Else
        Range("C2").Font.Color = vbBlack
    End If
    If Target.Address = Range("B2").Address Then
        Application.EnableEvents = False
        If Range("B2").Value2 = "True" Then
            Range("C2").Calculate
            Range("C2").Font.Color = vbBlack
            Range("B2").Value2 = "False"
        End If
        Application.EnableEvents = True
    End If
End Sub

Must set Calculation to "Manual".

 

[chazrab]

What about something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("C2").Value2 <> Evaluate("=SUM(C3:C7)-SUM(C8:C26)") Then
        Range("C2").Font.Color = vbRed
    Else
        Range("C2").Font.Color = vbBlack
    End If
    If Target.Address = Range("B2").Address Then
        Application.EnableEvents = False
        If Range("B2").Value2 = "True" Then
            Range("C2").Calculate
            Range("C2").Font.Color = vbBlack
            Range("B2").Value2 = "False"
        End If
        Application.EnableEvents = True
    End If
End Sub

Must set Calculation to "Manual".

Thanks for helping with this - don't know why, but I'm having trouble understanding what this code is doing - why and what is the purpose for Target.Address, etc.

As a side note - there's one thing I am realizing - setting a manual calculation in order not to have
the formula update for an added credit card entry cancels out when the calculation is reset back to automatic - and that is necessary for the purpose of valid formula calculations for bank entries...
so I don't know if what I want is possible - reset the automatic calculation yet
asking it to not include an entry in the result of the formula calculation.

 

[veryamusing]

why and what is the purpose for Target.Address

Target.Address is there to check whether you have toggled the calculation flag. In the worksheet, there's a data validation with a list of these items: True,False. If the user changes False to True, then the formula is calculated. If the user is changing any cell other than Range("B2"), nothing happens. The only time the range is calculated is when the drop down menu item is changed to True from False.

I will keep thinking about your problem, and post back if I can think of something else...

 

[codeliftsleep]

this is simplistic...
use the button click event

private sub Button1_Click()

Sheet("MySheet").Range("A30") = 'Whatever you want it to be
End sub

private sub Button2_Click()

Sheet("MySheet").Range("A30") = 'whatever you want
End sub

However, the programmer in me cringes because that is a really bad implementation. Personally, I'd rethink how you are setting this up rather than continuing like this. You'll thank me later.

 

[chazrab]

this is simplistic...
use the button click event

private sub Button1_Click()

Sheet("MySheet").Range("A30") = 'Whatever you want it to be
End sub

private sub Button2_Click()

Sheet("MySheet").Range("A30") = 'whatever you want
End sub

However, the programmer in me cringes because that is a really bad implementation. Personally, I'd rethink how you are setting this up rather than continuing like this. You'll thank me later.

what about sum by font color? My thinking as to the steps:

1.  user enters a value in Textbox1 and clicks on Credit card Btn1
2.  code in Btn1 places Textbox1.value on correct row, say 17 of column and colors it light blue
3. Sum formula in row 30 changes from =(SUM(BE3:BE7)-SUM(BE8:BE26)) to a formula that sums only cells in colnum <>= light blue
4. Result: formula stays intact for summing correct balance calculation for values <>= to light blue and credit card balance also visible on row 31 has correct new balance = oldbalance +Textbox1.value

Is it possible to do it this way ? - by NOT summing by fill color, but by FONT color and NOT using a For Each loop for every cell in column
to test for Font color if at all possible. This way, the correct balances will always show. Maybe this would be a more elegant way.

Thanks for any help, guidance and suggestions.
cr