Hiding Unhiding Rows based on Text condition

**Winner_texas** · 10-07-2010, 04:22 PM

Dear all,

I am trying to find a way to hide and unhide rows based on Textual condition.

I am visualizing 3 checkboxes. When the checkbox is checked, it will hide based on aCondition. And similarly for the other two checkboxes.

The condition is whether the row contains the word "Current" - Condition for the first checkbox

The condition is whether the row contains the word "Improvement" - Condition for the second checkbox

The condition is whether the row contains the word "new" - Condition for the third checkbox

If there is any other simple way to do this, please let me know. I dont want to use the Group/Ungroup option because it doesnt look good and can be confusing. Thanks a lot. Please see the file attached.

**Leith Ross** · 10-07-2010, 04:30 PM

Hello Winner_texas,

Which row or rows are connected to each check box?

**Winner_texas** · 10-07-2010, 04:35 PM

Originally Posted by Leith Ross

Hello Winner_texas,

Which row or rows are connected to each check box?

They are currently not connected. Thats what I am asking help for.

Eventually,

When I click on Checkbox 1, it should hide all the rows which contain "Current" in Column A.

When I click on Checkbox 2, it should hide all the rows which contain "Improv" in Column A.

When I click on Checkbox 3, it should hide all the rows which contain "New" in Column A.

Also, when I unclick on the checkbox, it should show those rows which were previously hidden.

If there is any confusion, please ask. I am monitoring this thread all times. Thanks a lot.

**Winner_texas** · 10-07-2010, 05:28 PM

I found this code on this forum and I am thinking that what I am asking for could be a variation of this? Thanks again in advance.

Please Login or Register  to view this content.

**Leith Ross** · 10-07-2010, 11:48 PM

Hello Winner_Texas,

When hiding rows using worksheet controls, it is best to dedicate a row to the controls at the top of the worksheet. If you don't the controls will be hidden along with the row they belong to.

I have placed the controls on row 1 and frozen the row so it is always visible. The Check Boxes call two different macros. One to hide the rows and another to restore them. Here is the code that has been added to the attached workbook.

Module1 Code

Please Login or Register  to view this content.

Sheet1 Code for the Check Boxes

Please Login or Register  to view this content.

**Winner_texas** · 10-08-2010, 11:58 AM

Originally Posted by Leith Ross

Hello Winner_Texas,

When hiding ........
End Sub
[/code]

Thanks a lot for writing this code and for your time. Few points -

1. How do I change the name of the the checkboxes in Cell A1 in your solution? For example if I would like to change it to "Hide Current" instead of "Current"..and similarly for others.
2. Can you give me a little logic around your code lines so that if I need to edit/learn, I can do it myself. For example, what if I instead of "Improv", I have "Improvement" in all the lines. I didnt see a reference to word itself in the code. I am wondering if it is dependent on the control name itself.
3. Later on in this development, if I have another condition such as - Hide all rows which have "Final" word in the text. How do I replicated what you have done.

Once again, this is great. My questions may be very naive since I have never done coding but willing to learn if I can make tools such as these.

Many Thanks

**Leith Ross** · 10-08-2010, 01:46 PM

Hello Winner_texas,

Question: 1. How do I change the name of the the checkboxes in Cell A1 in your solution? For example if I would like to change it to "Hide Current" instead of "Current"..and similarly for others.

Answer 1: These are Control Toolbox Check Boxes .

Open the VB Editor by pressing ALT+F11
Place VB in Design Mode using ALT+R then pressing M
Under the VBAProject for your workbook, look for the Microsoft Excel Objects
Double Click Sheet1(Sheet1)
Open the Properties window using F4
Click the drop down in the Properties - Sheet1 window.and Click CheckBox1
Find the Caption property in the list. Select the text in cell to right and type in your new caption.
Click the drop down in the Properties - Sheet1 and repeat steps G and H for the remaining check boxes.

Question 2: Can you give me a little logic around your code lines so that if I need to edit/learn, I can do it myself. For example, what if I instead of "Improv", I have "Improvement" in all the lines. I didn't see a reference to word itself in the code. I am wondering if it is dependent on the control name itself.

Answer 2: The macro HideRows takes two arguments. The first argument is the Search Term and the second is the unique ID number used to identify the control. In the example below, the Control's Caption is the Search Term and 1 is the Control's ID.

Please Login or Register  to view this content.

The search is not case sensitive and you can include wildcard characters in the search term. If you want to look for for either Improv or Improvement then your search term would be "improv*". This will match all words beginning with "improv" like "improve", "improvise", and "improvement".

Please Login or Register  to view this content.

The Control's ID is used an index into the Public array HiddenRng. This array contains the Range (rows) that were hidden by the macro. Storing this range allows the macro to restore the hidden rows when the check box is cleared.

Question 3: Later on in this development, if I have another condition such as - Hide all rows which have "Final" word in the text. How do I replicated what you have done.

Answer 3: If you add another Check Box to hide rows with "Final" in them it look like this.

Please Login or Register  to view this content.

Module1 Code
You also need to increase the size of the array HiddenRng to 1 less than the number of controls you have on the sheet . That is because this is a zero based array.

Please Login or Register  to view this content.

**Winner_texas** · 10-08-2010, 02:54 PM

Originally Posted by Leith Ross

Hello Winner_texas,

...
[/code]

Thanks a lot. Most of the code makes sense to me and I should be able to at least modify it for trivial changes.

I made the changes to the captions and added a fourth control, however it is not working properly. Its hiding only the second row. Please advise.

**Winner_texas** · 10-08-2010, 03:02 PM

Just thinking about it more, I believe that I am seeing that problem because the Search Term is not the Control's Caption anymore?

**Winner_texas** · 10-08-2010, 04:07 PM

So I took the "asterik" out from the code and the macro is working now.

"Private Sub CheckBox1_Change()
If CheckBox1 = True Then
HideRows "improv*", 1
Else
ShowRows 1
End If
End Sub"

**Winner_texas** · 10-08-2010, 04:24 PM

Originally Posted by Winner_texas

So I took the "asterik" out from the code and the macro is working now.

"Private Sub CheckBox1_Change()
If CheckBox1 = True Then
HideRows "improv*", 1
Else
ShowRows 1
End If
End Sub"

So this Macro works beautifully and very easy to edit. I was able to add another control box to hide specific rows and its working without any problems.

I would like to add to it now. Hope it will be very similar to the code above. If I want to "Show Only" based on some text, how would I do it. I added some rows which say "Cash Flow" in it. When I click on the 5th Control Box, it should only show the rows which has Cash Flow in the first column. I have added the box to save your time. Once again, thanks for your help.

**Leith Ross** · 10-08-2010, 04:24 PM

Hello Winner_texas,

Strange, I downloaded the file and it is working correctly.

**Leith Ross** · 10-08-2010, 04:32 PM

Hello Winner_texas,

Here is the macro. It simply activates the other check boxes . Hiding all the other data leaves only the Cash Flows (if any are present).

Please Login or Register  to view this content.

**Winner_texas** · 10-08-2010, 05:48 PM

Originally Posted by Leith Ross

Hello Winner_texas,

Here is the macro. It simply activates the other check boxes . Hiding all the other data leaves only the Cash Flows (if any are present).

Please Login or Register  to view this content.

Leith, Thanks for the above solution and I apologize for continuous questions. The above solution will not work in absence of the above 4 checkboxes. It would be good to have a macro which only looks for the particular text on the basis of which we want to show the rows and hides everything else. So, to paraphrase, if the 4 initial control boxes were not there, how will we still accomplish the same task. Thanks!

**Leith Ross** · 10-08-2010, 07:08 PM

Hello Winner_Texas,

I wanted to make this change as simple as possible. So, I added another argument on to the HideRows macros. The arguments now are: Match Term (string), ControlID (integer), HideOnMatch (boolean). The last argument determines if the row is to be hidden when the match term, what you are searching for, is found (True) or to hide all other rows that do not match the search term (False). Here are the updates that have been added to the new workbook.

Module1 Macro Code

Please Login or Register  to view this content.

Sheet1 CheckBox Macro Code

Please Login or Register  to view this content.

.

**Winner_texas** · 10-11-2010, 11:54 AM

I am currently playing with it. Thanks a lot for your time and help. Also, thanks for simplifying it for me. Best regards.

Hiding Unhiding Rows based on Text condition

LinkBack

Thread Tools

Rate This Thread

Display

Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

This MACRO works and we are good ..related question

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Re: Hiding Unhiding Rows based on Text condition

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions