View Full Version : Solved: Dynamic Controls

05-27-2009, 03:00 PM
Hey, I am using Excel 2007, and i have a dynamic list of customer records in a table. I am using vba to add and remove records from the table. For each record in the table I want there to be a drop down list with certain actions such as "Edit" and "Delete". Since there is an unknown amount of records I have to add the drop down box when the record is added to the table. I was wondering what the best way would be to dynamically add this drop down list with vba when the record is added to the table, and then remove the list when it is deleted. Thanks in advance for any ideas.

- Mitch

05-27-2009, 03:09 PM
Create a row at the end of the table with all of the formats that you want, then insert a row above this row every time you add an item.It will inherite those formats then.

05-27-2009, 03:19 PM
Hey xld, I need it to make the same activex controls every time a row is added, I tried what you said and yes it keeps the same formatting but doesn't make a new drop down list, can you please elaborate?

05-27-2009, 03:33 PM
If all you want to do is add a "Edit","Delete" drop down, you could put Validation (list style) on one column. And drive those routines with a Change event routine. Fill Down and Delete Cells is more stable than repeatedly adding/deleting controls.

05-27-2009, 03:38 PM
Oh, ok. I'm more of a programmer, not very great with excel. So, just to clarify, I need to use the Worksheet_Change() event, or is there a more specific change event i could use?

05-27-2009, 03:40 PM
and what about having a checkbox in one column, two of my columns are also yes/no fields, and i would like to have a checkbox to make it easier

05-27-2009, 05:13 PM
1) The Worsheet_Change event was the one that I was talking about.

2) Instead of a checkbox (I'm steering away from having to make controls on the fly), you could use the BeforeDoubleClick event to bring up your Edit/Delete routine. Double click on a row and the user edits that customer. What is your Edit/Delete routine, a UserForm?
(If the Double Click is already spoken for (e.g. it sorts), you could use the BeforeRightClick event.)

05-28-2009, 11:11 PM
Thanks for the help guys, I ended up using controls since i realized i didn't need a drop down list. i used the simple form controls rather than the activex to add two checkboxes and a remove button to each row.

The double click event is a good idea for the edit row, so i can have every cell protected, and use the double click event to make that row unprotected and set the focus.

Thanks for the input, i was completely lost!