+ Reply to Thread
Results 1 to 11 of 11

Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

  1. #1
    Registered User
    Join Date
    02-17-2013
    Location
    Charlotte NC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    Hi Everyone, I posted this on another board but have not heard back anything yet.

    I am new to the board and with minimal VBA experience, and I am hoping that someone can help me with the following:

    I have a sheet with hundreds of rows and columns, which I want the end-user to easily be able to use checkboxes to hide/unhide any columns or rows they wish based on three sets of values in the column headers and row headers.

    Please see the simple example attached.. I have tried several different pieces of code for the checkboxes but nothing has worked for both rows and columns yet.

    For instance, if a user unchecked all boxes except for "Apples" and "3", it would only display columns with "3" in row 5 and only display rows with "Apples" in column A.

    Any help would be greatly appreciated. Thanks to all!
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    I have attached your example back with the apples one done, you can copy the rest from there.

    You will need to adapt Row to be Column in the others, this should give you a good start.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-17-2013
    Location
    Charlotte NC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    Thanks Nathan, this work for apples in the example but I have a few more follow up questions to your solution:

    1)Do you know of code that executes this faster? The current one takes 20 seconds or so per click when I increase the rows in the example toward 1000 where I need it. I think it is because it is moving from row to row one at a time. Do you know of code that quickly looks at the complete range at once and hides/unhide based on the criteria. I have seen this done instantly in the past but cant remember how it is done.

    2) Right now the unchecked shows the rows. I need the checked to show the rows and uncheck to hide them. I tried simply changing False to True and vice versa in your version, but that wouldnt do it.

    3)I'm having issues getting that same code to work for the columns.. for instance, I attempted "Dollars" and "Account A" in the attached with your code and swapped all rows for columns, new ranges,etc, but I am getting a type mismatch error.

    See attached
    Thanks so much! Very Appreciated.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    Please Login or Register  to view this content.
    I dont have much time today, so this is a good point to start at.

  5. #5
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    Hi nathansav
    try this (using auxiliary row 3 and auxiliary column B)

  6. #6
    Registered User
    Join Date
    02-17-2013
    Location
    Charlotte NC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    Thanks Nilem! This is awesome and almost perfect.. I just need the user to be able to select multiples checkboxes if they wish, for any of the criteria. Right now the code looks to be mutually exclusive. For instance, ability to select Account A, Account B, Dollars, Units, Apples, Oranges - and have only those rows/columns exposed.

    Thanks again.. getting me so close.

  7. #7
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    In this case you will need a button. Try

  8. #8
    Registered User
    Join Date
    02-17-2013
    Location
    Charlotte NC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    Thanks so much Nilem!! This works beautifully and I love the button addition as well. I'll let you know if any other questions, but am all set for now.

    Thanks again. Will changed to solved.

  9. #9
    Registered User
    Join Date
    02-17-2013
    Location
    Charlotte NC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    Hey Nilem, can you please save me one more time here.. cant figure out why the attached is not working. All I did was try to swap the ActiveX checkboxes for Form checkboxes but must have screwed something up in the transfer.

    I appreciate any advice on how to handle. Can even send some cupcakes to Russia or something if need be.

    Thanks
    Attached Files Attached Files

  10. #10
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    Hi Gravy258
    try it (I ungrouped checkboxes)

  11. #11
    Registered User
    Join Date
    02-17-2013
    Location
    Charlotte NC
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Checkbox VBA - Hide/Unhide Rows and/or Columns based on cell values

    Perfect, thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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