Consulting

Results 1 to 8 of 8

Thread: Solved: Dynamic Controls

  1. #1
    VBAX Newbie
    Joined
    May 2009
    Posts
    5
    Location

    Solved: Dynamic Controls

    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

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie
    Joined
    May 2009
    Posts
    5
    Location
    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?

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.

  5. #5
    VBAX Newbie
    Joined
    May 2009
    Posts
    5
    Location
    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?

  6. #6
    VBAX Newbie
    Joined
    May 2009
    Posts
    5
    Location
    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

  7. #7
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.)

  8. #8
    VBAX Newbie
    Joined
    May 2009
    Posts
    5
    Location

    Smile

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •