Using MultiSelect List Box output to insert new lines in sheet


Active Member
Jun 15, 2005
I have a multi select listbox which presents me with a list of staff - lbStaff.

I select the different staff I want to assign to a particular project which is in a sheet's named range called tblProjects.

The listbox is presented in a pop-up form which is activated when I dbl click on the project name in tblProjects. The row where the project sits is ProjectRow and it will have a value from 1 to the last row of the table.

Each time I make a staff selection I need a macro to:
1. Work out how many staff have been selected in the lbStaff listbox
2. At the line ProjectRow, insert the number of rows (less1 for the first row already existing) for the number of staff selected such that each staff selected has a new row beneath the project.
3. Then copy formulae and text and formats from the initial row (ProjectRow) to these new rows
4. Transfer staff names to Col. C from lbStaff to each row created in against the project.
4. Close the pop up form

I can get the lbStaff list box populated OK with my staff pool but cant figure out next steps for transferring selection to new rows generated by the number of staff selected.

What I need to end up with is an automatically expanding tblProjects list of projects with selected staff in Col C. Some projects will have just one row for those with one staff member, others might have five rows for the five staff selected to the project. There are two blank lines between each different project.

Any great code ideas out there I could use to crack this one?

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...