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:
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.
8 Responses to “Introducing PHD Sparkline Maker – Dead Simple way to Create Excel Sparklines”
This looks like it could be very useful for a project I'm putting together right now, thank you so much. Quick & silly question, how do I copy & paste the sparkline as a picture?
Question answered. For anyone else:
Select chart>Hold Shift key & select Edit/Copy Picture>Paste
[...] more information about PHD Sparkline Maker, please read this article and to learn more about Sparklines, read this article from Microsoft Excel 2010 blog. Also there [...]
Am I right in thinking that the y-axis is set automatically by excel?
That makes it possible to get the column chart not to start at zero.
Andy - yes, it is currently set to 'auto', which defaults to a zero base for positive values, but you can change that by left-clicking the chart, then choosing (in Excel 2007):
"Chart Tools/Layout/Axes/Primary Vertical Axis/More Primary Vertical Axis Options"
PUBLIC SERVICE ANNOUNCEMENT: When manually editing a chart's minimum/maximum axis values, PLEASE be sure there's a valid reason and that doing so won't skew the message shown by the data (e.g. by exaggerating differences). If in doubt, go back and read Tufte. (W.W.T.D.?)
[...] gridlines, axis, legend, titles, labels etc.) and resize it so that it fits nicely in a cell [example]. This is the easiest and cleanest way to get sparklines in earlier versions of excel. However this [...]
thanks for the work creating the template!!!!
looks good