+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    05-23-2018
    Location
    Liphook, England
    MS-Off Ver
    2013
    Posts
    4

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

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    5,501

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

    Replace this line of code:
    Please Login or Register  to view this content.
    with this line:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    05-23-2018
    Location
    Liphook, England
    MS-Off Ver
    2013
    Posts
    4

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

    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!

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    5,501

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

    I'm not sure where you heard that changing the cell in a data-validated list in the drop down box wouldn't fire the change event to run the macro. That is not the case. It will work perfectly fine. Please explain in words, step by step, exactly what you want to do. You don't have to use named ranges. Explain in detail using a few examples from your data and referring to specific cells, rows, columns, ranges and worksheets what ranges you want to copy.

  5. #5
    Registered User
    Join Date
    05-23-2018
    Location
    Liphook, England
    MS-Off Ver
    2013
    Posts
    4

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

    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.

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    5,501

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

    Try the attached file.
    Attached Files Attached Files
    Last edited by Mumps1; 09-26-2018 at 09:22 AM.

  7. #7
    Registered User
    Join Date
    05-23-2018
    Location
    Liphook, England
    MS-Off Ver
    2013
    Posts
    4

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

    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!

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    5,501

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

    You are very welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1