Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-29
9
Medium Priority
?
151 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
[X]
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
  • 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 31

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
Independent Software Vendors: 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 31

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 31

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 31

Accepted Solution

by:
gowflow earned 2000 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 31

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

As freelancing is becoming more and more common in the tech industry, certain obstacles are proving to be a challenge to those who are used to more traditional, structured employment. This article is meant to help identify such obstacles and offer a…
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.

618 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