Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

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

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
DougDodge
Asked:
DougDodge
  • 5
  • 3
1 Solution
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
gowflowCommented:

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
 
DougDodgeAuthor Commented:
All or nothing.
Checked show them all. Uncheck none of them show.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
gowflowCommented:
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
 
gowflowCommented:
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
 
DougDodgeAuthor Commented:
This is it...... for the Rotation Calendar

Huge changes coming in the Site Progress Workbook.... (Stay Tuned)
Rotation-Calendar-V04.xlsm
0
 
gowflowCommented:
Sorry do not understand why you posted V04 ???
Did you see the one I posted V03 ?

gowflow
0
 
DougDodgeAuthor Commented:
V03 works perfectly..... V04 was just date changes that do not effect any of your work.....

Thank you once again.....
0
 
gowflowCommented:
ok great will keep monitoring this question for any further help you may need pls post a link in here.

gowflow
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now