+ Reply to Thread
Results 1 to 12 of 12

Add Range/s to Array Based on CheckBox value being True

  1. #1
    Registered User
    Join Date
    06-13-2018
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Add Range/s to Array Based on CheckBox value being True

    Hi All,

    I hope so one can help me, I have a UserForm with 9 Checkboxes (1 to9) and each Checkbox relates to a specific range of cells from 9 separate worksheets.

    I have set the ranges to Rng1 to Rng9

    I want to add only the ranges (Rng1 to Rng9) that have been checked as true to an array called RngArray.

    Is there any easy way to loop through the checkboxes and only add those that have been checked to the array?

    Many thanks in advance.

    Andy

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Add Range/s to Array Based on CheckBox value being True

    Sure - but Excel should not have allowed you to create a range named Rng1, since that is a valid cell address (unless that is the name of your checkbox???). In my code, I used rngR1 to rngR9 as the range names, and checkbox1 to 9 as the checkbox names - you might want to make RngArray public or global (or not) depending on what you plan on doing with the values.

    Please Login or Register  to view this content.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    06-13-2018
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Re: Add Range/s to Array Based on CheckBox value being True

    Hi Bernie,

    Many thanks for the reply and code, I'm getting a Run-time '1004': Methid 'Range' of object'_Global' failed erroe for the following line:

    ReDim Preserve RngArray(1 To j + Range("rngR" & i).Cells.Count)

    I can't figure out why, do you have any ideas please?

    Andy

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Add Range/s to Array Based on CheckBox value being True

    I can't figure out why, do you have any ideas please?
    As you have not uploaded a sample file depicting your actual file setup...It is going to be very difficult answering that question...It all depends on You using Bernie's provided code correctly...
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Registered User
    Join Date
    06-13-2018
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Re: Add Range/s to Array Based on CheckBox value being True

    Quote Originally Posted by sintek View Post
    As you have not uploaded a sample file depicting your actual file setup...It is going to be very difficult answering that question...It all depends on You using Bernie's provided code correctly...
    Hi sintek,

    This the code from by form.

    What I'm trying to do is is use Checkbox1 to 9 to chose which ranges are copied to a word document as an audit report. I've had i working by sending all ranges to the word document as desired, it's just not all
    range are required for every use so i want to be able to pick which ranges i send to create the report.

    Please Login or Register  to view this content.
    Many thanks in advance

    Andy

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Add Range/s to Array Based on CheckBox value being True

    My code was written on the assumption that your ranges were named ranges and not range objects. You can change each of your Set lines from

    Please Login or Register  to view this content.
    to this. Note that the Range name digit - in this case 1 - needs to match the checkbox name, like so the above set should become:

    Please Login or Register  to view this content.
    I also assumed that your ranges were single row or column - we need to index through by row and column instead of just by cells, so change this

    Please Login or Register  to view this content.
    To this (add a Dim m As Integer in the declarations) - all of your ranges are 5 columns wide
    Please Login or Register  to view this content.
    and after the looping is done, you need to transpose the array

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    06-13-2018
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Re: Add Range/s to Array Based on CheckBox value being True

    Many thanks that has got rid of the error, however it has now thrown up another error in the section code where it loops through copying the ranges to the newly created word document:

    Please Login or Register  to view this content.
    The error is Run-tine error '424 object required on:

    Please Login or Register  to view this content.
    Andy

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Add Range/s to Array Based on CheckBox value being True

    OK. Here's the thing - you either work with ranges, or you work with the array of values that you create. Not both - one or the other. So if you want to work with the array, then use the filled array to place the values into the word doc. If you want to use .Copy, don't create the array and just copy the ranges one by one and paste them into the word doc. Two different approaches, two different code requirements.

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Add Range/s to Array Based on CheckBox value being True

    Administrative Note:

    Welcome to the forum Andy.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here. Please note that you have several people chasing around in each place not knowing what the people in the other place are doing, which can create a duplication, and possibly waste, of effort.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  10. #10
    Registered User
    Join Date
    06-13-2018
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Re: Add Range/s to Array Based on CheckBox value being True

    Apologies I wasn’t aware of this requirement, my original query has now been solved via help on the other forum.

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Add Range/s to Array Based on CheckBox value being True

    Quote Originally Posted by Andyw111 View Post
    Apologies I wasn’t aware of this requirement, my original query has now been solved via help on the other forum.
    This is exactly why we ask you to post a link. We would still like to have the link here so that people get the whole picture if they read your thread.

    Please take the time to review our rules. There aren't many, and they are all important.

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

  12. #12
    Registered User
    Join Date
    06-13-2018
    Location
    England
    MS-Off Ver
    2013
    Posts
    9

    Re: Add Range/s to Array Based on CheckBox value being True

    Hi 6StringJazzer

    I tried to post the link with my last message but got a message some thing along the lines that I need to post more messages before I can share links or quote posts.

    Anyway the final code that does exactly what I was looking for is:

    Please Login or Register  to view this content.
    Many Thanks

    Andy

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Set Userform Checkbox value to True if CheckBox.Caption string is found in another String
    By TFiske in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-28-2018, 03:39 PM
  2. Replies: 6
    Last Post: 01-30-2017, 03:07 PM
  3. [SOLVED] Print array of sheets based on True/False values
    By majime01 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-28-2015, 01:50 AM
  4. Replies: 4
    Last Post: 03-27-2013, 10:05 PM
  5. how to make checkbox true selecting then chaning 2nd box true when null
    By ferrum_equitis in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-30-2012, 01:13 PM
  6. Return subsequent list based on true/false array
    By john. in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-22-2012, 02:23 PM
  7. Filling in a cell based on Checkbox true/false
    By Rivalwon in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-10-2007, 07:07 PM

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