Show hide list boxes using VBA

Posted on December 18th, 2012 in VBA Macros - 6 comments

Rama, one of our readers emailed this:

Hello Chandoo I am very new to vba. Help me with this

Q) I Have Many List boxes In That I need to Hide Few Of them Using Check box

Example:If I have List boxes Like A,A1,B,B1

If I Check On Check box A(Captioned As A) It Should show A,A1 List boxes. If I Unchecked it Should Hide A,A1 List boxes

In a similar manner if i checked Check box B .It Should show B,B1 List boxes. If I Unchecked it Should Hide B,B1 List boxes

Show Hide list boxes by using a check box

We can use check box and a bit of VBA to do this easily. First see this demo:

Show hide list boxes using Excel VBA - Demo

How to show or hide list boxes – Video

Although the concept behind this is very simple, explaining it in a post will make it very long. So I made a 10 minute video. Please watch it below:

[Watch this on our youtube page]

For more on this technique – see Customer Service Dashboard article.

To insert check boxes & list boxes see this tutorial.

Download example workbook

Click here to download the example workbook to understand this technique better. Examine the code in module 1 & 2 to know more.

How do you hide / show things using VBA?

Selectively hiding or showing is a great way to enhance your models, dashboards or reports. I use this technique very often. Most of my dashboards, products etc. contain interactive help that user can see or hide with a click. In background, I use few lines of VBA to do this magic.

What about you? Do you face similar situations? How do you handle them? Share your VBA tips & ideas using comments.

Are you new to VBA?

If so, you have hit a treasure chest. Start with our Excel VBA page and get the basics. Once you are ready to take a deep dive, go thru dozens of VBA / Macro Examples.

And when you want more, consider joining our VBA classes.

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

6 Responses to “Show hide list boxes using VBA”

  1. Waseem says:

    Morning Chandoo.. This is great post and shall make our life easier. I have a quick question. What code should I write if, instead of checkbox, I use a list control. In that case there would be values (1,2,3 or 4 etc.) instead of TRUE or FALSE. I need to know it for I would have more than two groups to display. Your input would help. Btw, I dont know anything about VBA so am just gonna copy your code :)

    • Luke M says:

      Hi Waseem.

      I think you could do something like this:

      Sub hideMultiples()
          ActiveSheet.Shapes.Range(Array(“grpList1″)).Visible = (Range(“A2″) = 1)
          ActiveSheet.Shapes.Range(Array(“grpList2″)).Visible = (Range(“A2″) = 2)
          ActiveSheet.Shapes.Range(Array(“grpList3″)).Visible = (Range(“A2″) = 3)
          ActiveSheet.Shapes.Range(Array(“grpList4″)).Visible = (Range(“A2″) = 4)
      End Sub

      Note that each line ends with a logic check. If A2 (or whereever you link your listbox to) is equal to the number, then the group is visible. Otherwise, it’s not. 

  2. Suresh says:

    Hi Chandoo
    It is really a useful one. I have a query how do I link the selected values in the list to Pivot Table. i.e how do i make pivot table updated using the selected values of the list box of the form control. It would help me lot in my work

    Regards
    Suresh 

  3. zur says:

    How the Pivot table are updated?

  4. Uriel says:

    Hi Chandoo. It is really a useful one and I can apply this knowledge to my work, thanks a lot!!
    I download the example workbook and doesn’t work fine, I think that its broken because the grpList1 & 2 doesn’t exist in the list of shapes.

Leave a Reply