Solved

How to use VBA code to filter / hide for checkbox entry

Posted on 2015-01-29
9
129 Views
Last Modified: 2015-01-31
I am trying to figure out how to use a checkbox to filter or hide cell values by a checkbox entry.

In my workbook I have "Yearly" worksheet, and worksheets for 1 thru 12 for each month of the year.

The checkbox is on the "Yearly" worksheet at cell AA23 - (Include Events)

Checking this checkbox should either show or hide the cell values in worksheets 1 thru 12

IE: Worksheet "1" A7 & A8 have formulas that display the events in the named range from worksheet "Yearly" in AE10:AH260

Each day of the month in worksheets 1 thru 12 have similar formulas which need to governed by this same ruling.

How to have this done is a difficult issue for me..... Help here would be appreciated.
Rotation-Calendar-V02.xlsm
0
Comment
Question by:DougDodge
  • 5
  • 3
9 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40579505
This solution might help - http://www.experts-exchange.com/Software/Microsoft_Applications/Q_28126674.html

Basically, your code should read the checkbox, then put a value on the sheet which you can then access.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40579534

I am trying to figure out how to use a checkbox to filter or hide cell values by a checkbox entry.

We hv a different issue here as you have formulas in each cell directly pulling the data from Yearly.
But let me get it right what you want is when you un-tick Events in Yearly is to have the whole list of Invoice Payment and Invoice Due etc... the whole Range of Events "NOT SHOW" these items every day and if it is selected then to SHOW all these items in every day of the month wherever they fall ??

Is that what you want ?  All or nothing ?

as in your question I note the word 'Filter' which would imply show a certain part .... etc and not necessarily All or nothing

So what is it exactly ?
gowflow
0
 

Author Comment

by:DougDodge
ID: 40579580
All or nothing.
Checked show them all. Uncheck none of them show.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40581206
I just noticed that you have moved the checkboxes around this we cannot do as they are linked to formulas with the cell underneath each one by moving them around without changing the reference to formulas makes debugging a hell of a problem.

Pls advise how you want them to be as they were in the previous version posted and all formulas linked:
Y23 Include Observed Holidays                                    AA23 Include Special Events
Y24 Include Working Days                                             AA24 Include Project  Key Milestones
Y25 Include Events
Y26 Include Statutory Holidays

The workbook that you posted have Following:
Y23 Include Statutory Holidays                                            AA23 Include Events
Y24 Include Observed Holidays                                           AA24 Include Special Events
Y25 Include Working Days                                                    AA25 Include Project  Key Milestones

As you can see the cells formula are referring to the previous layout and now the layout is changed so it will be very difficult in the future to troubleshoot any problem if you keep it like this all need to be reshuffled.

Pls advise what do you want so it is made once and for all.
gowflow
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 29

Expert Comment

by:gowflow
ID: 40581261
ok I guess we are on different time zones. I have readjusted the formulas to fix the workbook you posted which is the second block
Y23 Include Statutory Holidays                                            AA23 Include Events
Y24 Include Observed Holidays                                           AA24 Include Special Events
Y25 Include Working Days                                                    AA25 Include Project  Key Milestones

and also included a routine in macro to handle showing/hiding events when Include Events check box is cliked or not clicked.

PLs check the whole workbook and let me know.

PS Important
You should NOT Change the layout of the checkboxes (without making sure the formulas have been revised) as per my explanations on the past thread or else you are setting yourself for major trouble in the future case you want to add/delete items or checkboxes and it will become a nightmare to figure out which is which as you have already a very complicated workbook with Names formulas and formulas as it is already.

gowflow
Rotation-Calendar-V03.xlsm
0
 

Author Comment

by:DougDodge
ID: 40581387
This is it...... for the Rotation Calendar

Huge changes coming in the Site Progress Workbook.... (Stay Tuned)
Rotation-Calendar-V04.xlsm
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40581425
Sorry do not understand why you posted V04 ???
Did you see the one I posted V03 ?

gowflow
0
 

Author Closing Comment

by:DougDodge
ID: 40581474
V03 works perfectly..... V04 was just date changes that do not effect any of your work.....

Thank you once again.....
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40581579
ok great will keep monitoring this question for any further help you may need pls post a link in here.

gowflow
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a nutshell Dropbox is a cloud-based data storage service which synchronizes with data files on your computers. The Dropbox folk provide 2GB of free storage but if you need more you can sign up for a 50GB or 100GB subscription account. I pr…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now