Using MultiSelect List Box output to insert new lines in sheet

scoha

Active Member
Joined
Jun 15, 2005
Messages
428
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?
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,141,142
Messages
5,704,529
Members
421,353
Latest member
jekoxien15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top