Userform CheckBoxes to Hide Rows.

[craigs23]

Hi All,

I am returning to this board after som invaluable advice I recieved previousley.
My question this time relates to the use of CheckBoxes on userforms.

Basically I have a Master worksheet which contains a list of documents in Rows 1 to 15
I then Have a userform with 15 tickboxes which mirror the list of doicuments on the master sheet.
The user then ticks the relevant checkbox if the document is required for this project.
I then want to hide the remainder of the rows (where Checkbox has not been ticked) which will then leave the user with a worksheet listing only the required (ticked) rows.

I have come up with the following code which works to some extent, but my question is do I need to replicate this code for each tickbox or is there a more effiecent way?

Thanks in advance:

Private Sub CheckBox_Click()
'hide or unhide Row:
If CheckBox1.Value = False Then
Sheet1.Rows("1:1").Hidden = True
Else
Sheet1.Rows("1:1").Hidden = False
End If
End Sub

 

[Luke M]

If you really have that many checkboxes, then sadly yes, each would need it's own code. VB doesn't know which checkbox is supposed to correspond with each row. Can speed up the code a little bit, since the checkbox provides a Boolean output, and write:

Private Sub CheckBox_Click()
'hide or unhide Row:
Sheet1.Rows("1:1").Hidden = Not (CheckBox1.Value)
End Sub

An alternative idea might be to use a ListBox, which allows multi-selection. You could then use the index number of selected items to determine which rows to hide/unhide. But that is a little more complicated to setup than just a checkbox.

 

[Hui]

Why not use Option Buttons on a User Form
They automatically only show 1 enabled at a time

Then use some code like

Private Sub OptionButton1_Click()
  HideRows (1)
End Sub

Private Sub OptionButton2_Click()
  HideRows (2)
End Sub


Sub HideRows(RowNo As Integer)
  'This does the work  
  Sheet1.Rows("1:10").Hidden = False
  Sheet1.Rows(RowNo).Hidden = True
End Sub

 

[NARAYANK991]

Hi Craig ,

There are two ways you can do this :

1. If you really want to use the checkbox selection , then all you need is one macro , and usage of the Application.Caller property , which will tell the procedure which checkbox has been checked.

2. Alternatively , you can use the LinkedCell or CellLink property ( depending on whether you are using an ActiveX control checkbox Form control checkbox ) and use a Worksheet_Change procedure to detect which checkbox has been checked.

There have been questions asked and answered on this topic earlier in this forum ; you should be able to find them using the search facility.

Narayan

 

[THenson]

I consider myself good with excel but I do not know how to write code...

But I'm wanting to create an interactive "ToDo" list. In this list, I want one column to be a "category" which I would use for my different roles. For example, the categories might be: Supplier Related, Internal Audits, ISO9001, Quality Engineering, Misc etc... and what I would like to do is have a series of check boxes at the top where I could check the "categories" or "roles" I wanted displayed. For example, rather than use the "filter" option, I would like to have the checkboxes at the top where I could select one or more and only the ones I have selected would show up in my list... I would also like to include one checkbox that says "show all"...

Can you help me with the code?

I also want to be able to also only show line items with a "priority" that I select also using a checkbox. Priorities would be,
>Priority 1 = Today
>Priority 2 = This Week
>Priority 3 = This Month
>Priority 4 = This Quarter
>Priority 5 = This Year
>Priority 6 = Future/Wish List

I would love to see an example of an interactive "ToDo" list or at least the code to only show rows that match a specific checkbox....

thanks in advance.
thenson

 

[THenson]

Here is a start. Would appreciate your input.

 

[THenson]

Below is a screenshot. At the top, I have 6 checkboxes. What I want to do, is for the "to-do list" to only show the tasks where the corresponsing checkbox is checked.

For example, if I check "Supplier Related" checkbox and "Internal Audits" checkbox, I only want to see those action items.

Anyone got a simple solution?

thanks in advance.
thenson

 

[Hui]

Instead of using Check Boxes what about using a slicer?

 

[THenson]

I'm not sure what "slicer" is. Will slice allow me to only show specific lines of my list depending on the role/category?