VBA Checkboxs

**Moepat** · 02-21-2011, 04:34 PM

Hi all,

I am trying to use VBA to make a scorecard for our business unit and we need it to display various metrics.

Two questions: First, I want it to be able to display as many metrics as the user chooses. To this end I have made checkboxes for various metrics. I'm very new to macros and VBA but I've managed to use the following code to display the data I need at the end of empty cells (to stop overwriting). My problem is that I cannot find a way to erase what had been posted in those cells if someone unchecks the box. I tried using the following code to stop that but it only erases the metric at the bottom, not neccasarily the one that is being unchecked. I've wracked my brains but can't figure out how to do it.

Please Login or Register  to view this content.

Second question is: is there any way that I can direct the macro to pull in all data from C4 to F6 from the linked spreadsheet or do I have to continue what I'm doing and pull each one separately?

**jwright650** · 02-21-2011, 05:26 PM

Welcome to the forum.

I'm not sure if I understand your question, .... do you have values displayed as imperial units, but you want the user to be able to view these same values in a metric unit by checking a checkbox?

If so, I envision you adding a helper column to equal the value in a referenced imperial formatted cell, and format the helper column in metric...then using your checkbox to display either unit of measure by referencing the appropriate cell.

**Moepat** · 02-21-2011, 05:32 PM

No, sorry for the confusion, not metric unit. Business Metric (a measure of a business's success over the past year). We have a few different measures of success (EBIT etc).

Basically what I need to do is show various tables of data that I'd like to be able to show and then remove by checking and unchecking a check box. However, I can't figure out how to code VBA to cause unchecking the box to remove what was shown when the box was checked.

Originally Posted by jwright650

Welcome to the forum.

I'm not sure if I understand your question, .... do you have values displayed as imperial units, but you want the user to be able to view these same values in a metric unit by checking a checkbox?

If so, I envision you adding a helper column to equal the value in a referenced imperial formatted cell, and format the helper column in metric...then using your checkbox to display either unit of measure by referencing the appropriate cell.

**jwright650** · 02-21-2011, 05:42 PM

.... are you overwriting your data when you check the box?(can't undo this once it's done)

I can't envision what you need, so rather than us guessing what you have, maybe post a sample workbook (a before and after worksheet).

There are some really great VBA'ers in this forum(not speaking of myself), so I'm sure someone can get you fixed right up once we see what you need to accomplish.

Are you game for using a userform?

**Moepat** · 02-21-2011, 06:12 PM

Originally Posted by jwright650

.... are you overwriting your data when you check the box?(can't undo this once it's done)

I can't envision what you need, so rather than us guessing what you have, maybe post a sample workbook (a before and after worksheet).

There are some really great VBA'ers in this forum(not speaking of myself), so I'm sure someone can get you fixed right up once we see what you need to accomplish.

Are you game for using a userform?

unfortunately i'm not allowed to post the workbook as it has privileged information about my firms finances but heres a general gist.

basically, there are four checkboxes. one represents revenue in 2010, costs in 2010, income in 2010, and participation in 2010. when i click on a checkbox, the corresponding data appears on excel cells.

now, we sometimes want to compare revenue versus costs. so i designed it such that checking the boxes for both revenue and cost causes figures for both to appear, one under the other.

i'm fine thusfar, but here's where the problem happens. if i checked revenue and costs and both appear fine. but lets say i then want to compare revenue and income. unchecking the costs box doesn't cause the data on costs to go away. i want to know how i could program it such that when i uncheck a box, the data that is shown when it is originally checked goes away.

i'm using ActiveX checkboxes right now. not really sure what userform is?

**jwright650** · 02-22-2011, 11:16 AM

Do not attach your original file with the sensitive data in it...just a working sample. Sometimes a picture is worth a thousand words...even better a sample file...LOL

Maybe copy your file and then take out or use fake names and data just make sure it is still working so we can debug it or add some suggestions to make it work like you want.

Just need to see how the checkboxes and data are all working together....sometimes it is something very simple that needs to be tweeked to make it function correctly.

I'm still new at all of this, but there are others here who can work some magic with VBA.

**Moepat** · 02-22-2011, 12:25 PM

Originally Posted by jwright650

Do not attach your original file with the sensitive data in it...just a working sample. Sometimes a picture is worth a thousand words...even better a sample file...LOL

Maybe copy your file and then take out or use fake names and data just make sure it is still working so we can debug it or add some suggestions to make it work like you want.

Just need to see how the checkboxes and data are all working together....sometimes it is something very simple that needs to be tweeked to make it function correctly.

I'm still new at all of this, but there are others here who can work some magic with VBA.

Haha, got it. Okay, I made a sample workbook with what I'd like to do with VBA and the pics are below. There aren't any macros running here and I just typed fictional numbers in by hand in Excel cells. But this is what I want to be able to do with the checkboxes. When I click a checkbox I want it to generate the data one row below whatever is above it. I can do that so far. But, when I uncheck the box, I want those corresponding rows removed.