Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2015-01-29
9
138 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
Manage your data center from practically anywhere

The KN8164V features HD resolution of 1920 x 1200, FIPS 140-2 with level 1 security standards and virtual media transmissions at twice the speed. Built for reliability, the KN series provides local console and remote over IP access, ensuring 24/7 availability to all servers.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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 …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to edit text. This includes Font, Spacing, Resizing, Color, and other special text options.
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 …

829 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