Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Apr 2008
    Location
    Jacksonville, North Carolina, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel 2007 macro

    I am wanting to use a check box to execute a formula in excel. I have attached an example. I want to be able to put a check mark in C4 which will cause the formula in G4 to execute and to show the actual result. I want the results to carry down the spread sheet. I have tried with my limited knowledge and can only get a TRUE or FALSE shown when the check mark is inserted. It would not continue the correct formula down the list; would only show ##### error. Can anyone help or advise?
    Attached Files Attached Files
    Charlie

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Charlie,

    Could you be a little more specific about what you want to happen when you click check boxes? I'm not sure what you are trying to accomplish.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Apr 2008
    Location
    Jacksonville, North Carolina, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am trying to create a simple budget tool. I have known amounts in the E and F columns. To track the flow of funds I would like for the formulas in the G column to only execute when a check mark is placed in the C column. At the same time I would like for it to show the result of the formula being ran. I have attached another sheet (2) to show. I used the "Format Control" but can not get it to do what I am trying. I have added notes in the cells on this attachment to hopefully better explain.
    Attached Files Attached Files
    Charlie

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You have set the cell link for the check boxes as Col G, this will put TRUE/FALSE (overwriting the formula) in col G when it is checked/unchecked. Set the cell link to col C (hide the results since you don't need to see the TRUE/FALSE). Then you can use the formula in G4:

    =IF(C4,IF(AND(ISBLANK(E4),ISBLANK(F4)),"",G3-E4+F4),G3)

    If checked it will use the G3-E4+F4, if false it will just be G3...

    Steve

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Charlie & Steve,

    I think from an accounting point of view you want the formula to read:
    =IF(C4,IF(AND(ISBLANK(E4),ISBLANK(F4)),G3,G3-E4+F4),G3)
    Since you need a value in col G for the next formula to work, assuming you don't pay the items in order. This way the formulas down the line will all work.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    Apr 2008
    Location
    Jacksonville, North Carolina, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That did the trick!!! Thanks a million, your awesome!
    Charlie

Posting Permissions

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