Multiple Checkboxes to Create a List

**robstark** · 07-25-2012, 02:18 PM

Hi All,

My first post-

I've been searching all over for a solution to my following quandary. Here's my situation...

I have a sheet with a list of all cities in MN. I need to be able to choose 5 of these cities (there are a few hundred to choose from). I want those 5 chosen cities to stand out from the hundreds that are not chosen. My initial thought was to create a checkbox for each city and have those checked cities fill another column when the value=true.

Then I saw an older post that changed the properties of the cell so when clicked it changed color. I thought that was a great option, but every time the cell is clicked it flips from blank to color and I need the cell to remain unchanged when clicked (although double clicking might be a good alternative).

I'm totally open to a solution and I need to do this same exercise for ND. I'm fairly competent in excel, but not as advanced as most of you it seems. I have bare basic knowledge of macros and almost none in regards to VBA. That said I take direction EXTREMELY well and can comprehend information. I look forward to getting any instruction!

Thanks,
Rob

**ChemistB** · 07-25-2012, 02:32 PM

Are you sure you want to emphasis in place or would something like the attached work for you, A2:A6 has data validation dropdown menus. The rest is done with named ranges and VLOOKUP.

**robstark** · 07-25-2012, 02:53 PM

That is another option, but I need to have every MN city available and I think there are over 800 of them. That's a bit much for a drop-down list I think.

**ChemistB** · 07-25-2012, 03:12 PM

Good point.

Okay, I'm not a big fan of Checkboxes. Look at this file. Be sure to enable content so VBA works. Double clicking on column A places a check mark (marrett "a") in the cell. Conditional formatting turns the row red with white font.
To view code, right click on the sheet tab and "view code"
Would this work for you?
Another option would be combining the two; do the checkmark and then have formulas separate those values from the rest.
Any questions?

Edit: I added a file Cities2a.xlsm which does both checks and highlights then fills in a subtable. You can hide column H if you wanted to.