programming excel

Stuff that don´t fit in the other categories.
Post Reply
User avatar
Kirk
suck-o enforcer
suck-o enforcer
Posts: 547
Joined: 25 Apr 2009, 16:00
14
Contact:

programming excel

Post by Kirk »

does anyone here have some experience with programming excel? I use to know VBA but its been so long that I have forgotten it for the most part. im creating a spreadsheet that I need just a little bit of push on.

at the top under loan amount remaining I want it to start with the original loan of 220,000. I want it to decrease each time on of the paid check boxes is checked. I just cant recall how to do all this. (feeling pretty stupid lately buts its been nearly 2 years since I have been allowed to see a compter so...)

thanks,
kirk

Image

User avatar
lilrofl
Siliconoclast
Siliconoclast
Posts: 1363
Joined: 28 Jan 2009, 17:00
15
Location: California, USA
Contact:

Re: programming excel

Post by lilrofl »

I'll assume you have the developer tab enabled. There are two ways to do this really, although the Form Control is much simpler than the ActiveX method, at the cost of having a column reading logical values at you.

From the developer tab select the icon insert, and then the Form Control checkbox. Place a box next to every number you want to be manipulated by check.

Right click on a checkbox and select format control.

In the control tab link that checkbox to a cell that is otherwise unused. This cell will contain the True/False value we can use to determine if a value should be included in our function or not.

Under Loan Amount Repayment make a function to sum your numbers, such as:

Code: Select all

=SUMPRODUCT(($C$1:$C$10=TRUE)*$B$1:$B$10)
Which will check you C column rows 1-10 for True values, and sum any associated B column row.

Using the activeX checkbox you can script a macro to do the work for you instead of using a logical column... but I don't know activeX so I'm not a bunch of help there.
knuffeltjes voor mijn knuffel
[img]http://i911.photobucket.com/albums/ac320/stuphsack/Sig.jpg[/img]

Post Reply