Populate checkboxes based on drop down menu

**Broadway** · 10-15-2009, 03:33 PM

Hey!

Ok, so I am making a form. At the top there is a dropbox with a list of applications. Based on which application the user picks, I want another section of the form to populate check boxes with the name of the projects that are associated with that application. Some applications have 2 projects, some applications have 7 projects.

Anyone have any ideas? Thanks!

**Leith Ross** · 10-15-2009, 03:59 PM

Hello Broadway,

Welcome top the Forum!

If you post your workbook it will be easier and faster to get answers to your questions.

**Broadway** · 10-16-2009, 12:36 PM

I would upload the workbook, but I don't have one. I haven't started building the form yet, because I need to know if this is possible. If it is not then I need to try and think of a different solution. If you would like to see a mock up I could do that. I know the theory of programming and can read VB but I don't have an intimate understanding of the language, so I don't know whether something like this is even possible.

**Broadway** · 10-16-2009, 03:56 PM

I made a mock up of what the form will look like. There is no VB code yet, because I cannot find any way of doing this, but the mock up should let you visualize what I am trying to do.

I want to select and instance from the instance drop down, and then have the projects that are associated with that instance populate the projects area with check boxes.

Instance A has 7 projects associated with it
Instance B has 5 projects associated with it
Instance C has 2 projects associated with it

I hope this is more clear. Thanks for your time!

**jaslake** · 10-17-2009, 12:56 PM

You may want to use a UserForm for this exercise. The attached might get you started.
Hope this helps. J

**Broadway** · 10-19-2009, 11:15 AM

The userform looks like a great idea! I spent some time looking at them and how they work and built a new one from scratch. I am almost finished with it, but I am not sure about one thing.

The code for the button. Currently I have it clear the cells of the data page, and then set whatever is in the combo box as the instance. Now I just want it to do the same thing only with whatever is checked. I'm not sure how I should do this. I was trying to make and array and then save each checked value into it, and then reprint it to the data sheet, but I couldn't get it working. Does anyone have a better idea or some guidance?

Thank you!

**jaslake** · 10-19-2009, 11:50 AM

Looks like you made quite a bit of progress. Try this code for your Add Projects button

Please Login or Register  to view this content.

It worked for me on your post.
Hope this helps. J

**Broadway** · 10-19-2009, 01:23 PM

Worked PERFECTLY...you are awesome. Thanks so much for all the help!!

**Leith Ross** · 10-19-2009, 01:28 PM

Hello Broadway,

This workbook contains the check boxes. These will appear and disappear depending on the length of the displayed list. Another macro, a UDF, is included to allow you to check if the check box is checked or not. have a look and give me some feedback on the idea.

**jaslake** · 10-19-2009, 01:46 PM

Glad it works for you. I had a college professor who used to tell me

"It's not as important to know the answer as it is to know where to find the answer"

The answer to your question was found at http://www.ozgrid.com/VBA/multi-select-listbox.htm.
I noticed on your user form, you don't allow the user to close the form with the "X" in the upper right hand corner. I've used this technique in the past but I've always included a "Cancel" button. Don't want the user to get frustrated.

Just a thought. J

**Broadway** · 10-19-2009, 04:07 PM

jaslake - Yea I actually decided to do away with the code and let the user close the box with the X just because I found it annoying.

Ross- This is exactly what I was looking for to begin with! I am going to spend some time looking at it and learning how it works.

I have some scripting background in unix, but I just started looking into VB last week to do some projects at my job. You guys are definitely helping me out so much. Thanks to both of you for your time and help.

**Broadway** · 10-21-2009, 04:54 PM

Hey Leith, I need to change the background color of the drop down box...but Im not sure where you are setting it?

**Leith Ross** · 10-21-2009, 06:19 PM

Hello Broadway,

To change the color of the ComboBox (from Controls ToolBox):
1. open the Visual Basic Editor using ALT+F11
2. Press the keys ALT+R then the letter m. This puts you in design mode. To see the property sheet you have to be in design mode.
3. In the Project window, click on "Sheet1(Sheet1)"
4. Display the Properties window by pressing F4
5. Click the drop down in the Properties window and select ComboBox1
6. Select BackColor
7. Click the drop down button to the right and you will see the Color Pallette
8. Select your color.