Solved

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

Posted on 2015-01-29
9
141 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Google Sheets Script 21 95
Excel sheet question 12 55
Need to put a web footnote into a text book 7 54
Transposing this formula ... 5 24
Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

730 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