+ Reply to Thread
Results 1 to 9 of 9

Data Validation/Listbox multiselect

  1. 11-15-2016, 11:25 AM #1
    radddogg
    radddogg is offline
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Data Validation/Listbox multiselect

    Hi

    I have a dashboard with a bunch of dynamic validation dropdown lists that allow the user to refine data shown on the main report sheet. This controls the filtering of rows and columns.
    One of the filters is the 'ProNumber' and controls which columns to show. At the moment this works fine for one value but I want to do this for multiple values/columns.

    I have the values saved in a named range, the dashboard cell [Sheets("Highlight Report").Range("O3"] pulls the value through a data validation list and upon clicking the 'apply filters' command button the following code executes.

    Please Login or Register  to view this content.
    How can I achieve this? I'm thinking it will need to be a ListBox but never used one before and even if I get it to produce a comma separated list I'm not sure how to encorporate this into my code above.

    Any ideas? Cheers
    Reply With QuoteRegister To Reply
  2. 11-15-2016, 11:38 AM #2
    pjwhitfield
    pjwhitfield is offline
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: Data Validation/Listbox multiselect

    1. Cycle through the Listbox and add any Selected items to an Array.
    2. Loop through the columns as you are however for each column check it against each item in the array and if any match set a boolean variable to True then use that variable to decide if you should hide the column (after the loop in 2 has finished).
    If someone has helped you then please add to their Reputation
    Reply With QuoteRegister To Reply
  3. 11-15-2016, 12:13 PM #3
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: Data Validation/Listbox multiselect

    If you populate the ListBox with the values from L2:BH2 in that order, you could simply use the index number of each selected item in the list to reference the columns. e.g

    Please Login or Register  to view this content.
    This loops through each item in the listbox. The .Selected(ndx) returns True\False if an Item is selected or not.

    The listbox index numbers are zero-based. So ndx+12 is the column number (e.g. Listbox item 0 + 12 is column L).
    Last edited by AlphaFrog; 11-15-2016 at 12:18 PM.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.
    Reply With QuoteRegister To Reply
  4. 11-16-2016, 06:03 AM #4
    radddogg
    radddogg is offline
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: Data Validation/Listbox multiselect

    Thanks for the approach. I had got a few ideas on the approach, it was more the execution I was struggling with.
    Reply With QuoteRegister To Reply
  5. 11-16-2016, 06:19 AM #5
    radddogg
    radddogg is offline
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: Data Validation/Listbox multiselect

    Quote Originally Posted by AlphaFrog View Post
    If you populate the ListBox with the values from L2:BH2 in that order, you could simply use the index number of each selected item in the list to reference the columns. e.g

    Please Login or Register  to view this content.
    This loops through each item in the listbox. The .Selected(ndx) returns True\False if an Item is selected or not.

    The listbox index numbers are zero-based. So ndx+12 is the column number (e.g. Listbox item 0 + 12 is column L).
    Excellent, this works great. Had to add in the sheet reference to 'With ListBox1' but worked fine after. Only problem is its doing the opposite to what I need. By default if no selection is made all columns need to be shown. If a selection is made all other columns in the range should be hidden leaving only the selected columns. I know how to counter this by hiding the columns first then changing .hidden to .visible but I need to do a test on the listbox first to check if there is a selection. Something like;

    Please Login or Register  to view this content.
    But obviously the red line doesn't work as the object doesn't support the property.

    I'll keep looking but if you know a way round it please let me know. Cheers
    Last edited by radddogg; 11-16-2016 at 07:05 AM.
    Reply With QuoteRegister To Reply
  6. 11-16-2016, 09:47 AM #6
    radddogg
    radddogg is offline
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: Data Validation/Listbox multiselect

    Ok I'm nearly there.

    Please Login or Register  to view this content.
    This sets the hidden value to TRUE, how do I set it to FALSE?
    Reply With QuoteRegister To Reply
  7. 11-16-2016, 09:49 AM #7
    radddogg
    radddogg is offline
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: Data Validation/Listbox multiselect

    Ok pretty much got it now

    Please Login or Register  to view this content.
    Not very elegant but it seems to work.
    Last edited by radddogg; 11-16-2016 at 10:05 AM.
    Reply With QuoteRegister To Reply
  8. 11-16-2016, 01:48 PM #8
    AlphaFrog
    AlphaFrog is offline
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,659

    Re: Data Validation/Listbox multiselect 

    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 11-16-2016 at 02:08 PM.
    Reply With QuoteRegister To Reply
  9. 11-17-2016, 06:26 AM #9
    radddogg
    radddogg is offline
    Forum Contributor
    Join Date
    10-25-2011
    Location
    Preston, England
    MS-Off Ver
    Excel 2016
    Posts
    136

    Re: Data Validation/Listbox multiselect

    Cheers AlphaFrog

    +rep
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Generate multiple worksheets selected in multiselect listbox. Data is based on a table.
    By dylanrose in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-27-2015, 09:44 PM
  2. [SOLVED] Pass multiselect listbox data to email body
    By pjbassdc in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-25-2015, 03:35 PM
  3. [SOLVED] UF - sending multiselect listbox data to database
    By Raanan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-25-2013, 12:03 PM
  4. [SOLVED] Filling multiselect listbox based on closed workbook data
    By FKurzhals in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 08-23-2013, 02:56 PM
  5. [SOLVED] MultiSelect Listbox in Userform to copy data to worksheet
    By aarodn in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-06-2013, 03:52 PM
  6. Copy Selected items from multicolumn, multiselect listbox to another listbox
    By Willigb in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2013, 11:27 AM
  7. Use a Multiselect listbox to filter data
    By EoghanMBH in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-15-2012, 07:21 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1