Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Coding Ideas (2000)

    Could you provide more info or attach a small sample workbook?

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •