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?
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?
