1. ## Coding Ideas (2000)

Hello, I have a balance statement on a sheet which shows a projected balance after transaction are consolidated and a balance that shows present state of play before consolidation. Against my transactions I have tick boxes that when set true, add that transaction to the current state of play, similarly deduct if set to false. This is the code I have used on my one trial tick box and it works fine, however on my sheet there could be hundreds of transactions and I don't want to have to repeat the code many times over. Any solutions would be welcomed:

Private Sub CheckBox1_change()

If CheckBox1.Value = False Then

Range("I4").Value = Range("d4").Value + Range("H11").Value

Else

Range("I4").Value = Range("d4").Value

End If

End Sub

Thanks Darren.

2. ## Re: Coding Ideas (2000)

Having hundreds of check boxes on a worksheet seems rather unpleasant to me even without code, in particular if the number of transactions is variable.

Instead of having the user tick a check box, I'd let them enter something (an "X" for example) in a specific column, for example in column J. You can then use a simple formula in column I, for example in I4:

=D4+IF(ISBLANK(J4),0,H4)

This formula can be filled down.

3. ## Re: Coding Ideas (2000)

Thanks Hans, it is a better solution, I do only want one cell with the changing balance from consolidation of entries in the many cells however, how could I model this please?

Thanks D.

5. ## Re: Coding Ideas (2000)

Thanks Hans, looking at the outgoings details here, I would only want these to be deducted from the "balance at bank" when they have been consolidated ie, by putting maybe an X or whatever in the consol column. I11 is a working example.

D

6. ## Re: Coding Ideas (2000)

Try

=D4+SUMPRODUCT(ISBLANK(I11:I37)*H11:H37)

The values from column H are only included in the sum if the corresponding cell in column I is blank.

7. ## Re: Coding Ideas (2000)

Great Hans, Thanks V M

D.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•