Use drop down menu to copy and paste specific ranges of cells into text boxes

I am struggling with VBA as a complete novice and have bitten off a little more than I can chew!

I have 2 worksheets - RWM Venn Diagram Helper ('Helper') and RWM Venn Diagram ('Venn').

On Helper,I have some named ranges to collect individual's names based on end of term assessments in Reading, Writing and Maths and then the overlaps between each subject, eg. Reading and Writing, Reading and Maths, Writing and maths, and Reading, Writing and Maths.

On Venn, I have a Venn Diagram with a series of text boxes in each segment of the diagram. I wish to select an option from a drop down list - either 'End of Phase 1', 'End of Phase 2', or 'End of Year' so that the text boxes are populated with the appropriate names from the appropriate ranges on 'Helper'. Also, when the cell contents are pasted into the text boxes, I would like each name on a new line, if possible.

My macro is written in the Venn worksheet code but it does not copy and paste the data when the drop down menu is selected.

I have uploaded the example file - is anyone able to help me please?

Thank you Mumps1.

I've made your suggested change but it still doesn't copy the text though.

I have been told that where I had a data-validated list in the drop down box, changing the cell wouldn't fire the change event to run the macro. I have now changed this to a combo box but do not know how to have the code copy all cells with text in and paste them into the textbox. I recorded a macro whilst I carried out the actions that I would like to complete but this doesn't resolve the issue either as it does not run when the combo box is changed.

This is my new code:

Please Login or Register  to view this content.

Any further suggestions would be very gratefully received!

Thank you Mumps1 - your comments are reassuring!

For context: The sheets provided are part of a larger workbook which is being created to collect and analyse educational achievement data. Data is collected and analysed at 3 points through the year: 'End of Phase 1', 'End of Phase 2' and 'End of Year'.

I would like to achieve the following:

Step 1: Reading, Writing and Maths data is entered and calculations are automatically completed in RWM Venn Diagram Helper - Already created
Step 2: User selects an assessment point from the dropdown list in (C5) on RWM Venn Diagram - e.g. 'End of Phase 1' - Already created

The next step is where the issues lie:

Step 3: Text boxes (or similar) placed on top of the Venn Diagram in RWM Venn Diagram populate with the names of children from the appropriate columns AQ:AW in RWM Venn Diagram Helper in each segment of the diagram - Reading (AQ), Writing (AR), Maths (AS), Reading/Writing (AT), Reading/Maths (AU), Writing/Maths (AV), Reading/Writing/Maths (AW)

For Example: Selecting 'End of Phase 1' should populate a textbox in the Reading segment of the Venn Diagram with the names from RWM Venn Diagram Helper AQ4:AQ103 (the number of cells containing names will vary depending on the number of children that did not secure the required outcomes in the area(s) that each column represents) - in the sample, only two cells - 'Name 15' and 'Name 16' in cells AQ4 and AQ5 - should be copied into the text box on the Reading segment . The Writing/Maths segment would have three cells copied - 'Name 1', 'Name 2' and 'Name 3' from AV4:AV6. Any blank cells would be ignored.

The same process would be repeated if the user selected 'End of Phase 2' but using names from AX:BD. 'End of Year' would use columns BE:BK (incorrectly labelled as 'End of Phase 2' in Row 3 in the sample file.)

For ease of reading on the Venn Diagram, I would also like each name to be separated with a ";"

I hope this makes sense! I really appreciate you taking some time with this.

Thank you so much for this.

Learning VBA is exactly like learning a foreign language: I know enough to understand what you have written but don't know it securely enough to be able to write it myself. I really appreciate your time on this!