HideUnhide data as user selects item(s) from listbox

kaushik03 · Aug 12, 2012

Hello Friends,

I have data spread across from D12 to M25.

Col D contains name of individuals. From D12 to D25, we have 9 unique names.

For each name there are some data points from Col E to Col M

I have a listbox which is ( placed right above the dataset) filled with unique list of names( 9 such name).

My requirement is:

1.If user selects ‘A’ from listbox, I want only those rows to be populated which contains name ‘A’ at Col D and all other rows (with other names) should be hidden. Same applies for selecting any other name from listbox.

2.The listbox should have multiselection feature as well. I mean, If user selects two (or three four so on…) names, say A and B, the rows for A and B should be populated together keeping all other rows hidden.

3.If user hits checkbox, all selection from listbox should go away and all the data (all names) should be populated back.

I have attached a sample workbook herewith for your kind perusal.

https://hotfile.com/dl/166565988/50bd1a7/ListBox_hide_Unhide.xlsx.html

Hope I am able to make my points clear.

Regards,

Kaushik

shrivallabha · Aug 12, 2012

Place following code in Sheet1 module (right click on sheet tab | Select "View Code")

Following code is for filtering data. Assign this macro to listbox control via Right Click | Assign Macro option.

[pre]

Code:

Sub RunFilter()
Dim boolNone As Boolean
Dim varList(8) As Variant
Dim rng As Range

Application.ScreenUpdating = False

With Me

For i = 1 To .ListBoxes(&#34;List Box 2&#34;).ListCount
If .ListBoxes(&#34;List Box 2&#34;).Selected(i) Then
boolNone = True
varList(i - 1) = .ListBoxes(&#34;List Box 2&#34;).List(i)
Else
varList(i - 1) = &#34;False&#34;
End If
Next i
If boolNone = False Then MsgBox &#34;No item selected in Listbox!&#34;: Exit Sub

Range(&#34;D12:D25&#34;).EntireRow.Hidden = False

For Each rng In Range(&#34;D12:D25&#34;)
If Application.IsError(Application.Match(rng.Value, varList, 0)) Then
rng.EntireRow.Hidden = True
End If
Next rng

End With

Application.ScreenUpdating = True

End Sub
This code is for checkbox1 and it also goes in Sheet1 module.

Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
With Me
If .CheckBox1.Value = True Then
For i = 1 To .ListBoxes(&#34;List Box 2&#34;).ListCount
.ListBoxes(&#34;List Box 2&#34;).Selected(i) = False
Next i
.Range(&#34;D12:M25&#34;).EntireRow.Hidden = False
End If
End With
Application.ScreenUpdating = True
End Sub

[/pre]

kaushik03 · Aug 12, 2012

Hi shrivallabha,

Thank you very much for this solution...It is working perfectly fine..

May I ask you to help me with another query regarding this?

Actually I am importing data from some other sheet and pasting the same from D12 onwards at sheet1; and also copying the names from col D, pasting them from Q2 onwards, removing the duplicates to find out the unique list which I am filing in listbox.Basically, I have made the col Q dynamic by creating named range (with OFFSET-COUTNA formula) and passing that named ragnge (LstDyn)to 'input range' of listbox.

There is a fare chance that the unique list at col Q might change every time I import the data. So whenever, it happens, the listbox will be updated automatically.

I have seen that you have used 'varList(8)' array in your code which is hard coded as of now in accordance with my earlier requirement.

But considering my above requirement, can you please tell me how can we modify the code?

And also, before navigating to sheet1( from any other sheet) I want to unhide rows from D12 onwards(at sheet1), in case any row(s) are hidden (this piece of macro I want to assign to some other button)

I have uploaded the workbook (with named range) here:

https://hotfile.com/dl/166611293/8bdb594/ListBox_ide_Unhide.xlsm.html

Thank you again for your help dear..

Regards,

Kaushik

shrivallabha · Aug 12, 2012

Part AA] You will have to adjust in the following way.

Change the explicit declaration:

Code:

Dim varList(8) As Variant

to

[code]Dim varList() As Variant


Then Redim it based on the size of LstDyn. The code will look like:

[pre]Sub RunFilter()
Dim boolNone As Boolean
Dim varList() As Variant
Dim rng As Range

Application.ScreenUpdating = False

With Me

ReDim varList(.Range(&#34;LstDyn&#34;).Count - 1)

For i = 1 To .ListBoxes(&#34;List Box 2&#34;).ListCount
If .ListBoxes(&#34;List Box 2&#34;).Selected(i) Then
boolNone = True
varList(i - 1) = .ListBoxes(&#34;List Box 2&#34;).List(i)
Else
varList(i - 1) = &#34;False&#34;
End If
Next i
If boolNone = False Then MsgBox &#34;No item selected in Listbox!&#34;: Exit Sub

Range(&#34;D12:D25&#34;).EntireRow.Hidden = False

For Each rng In Range(&#34;D12:D25&#34;)
If Application.IsError(Application.Match(rng.Value, varList, 0)) Then
rng.EntireRow.Hidden = True
End If
Next rng

End With

Application.ScreenUpdating = True

End Sub

Part BB] If you want the sheet to have all rows visible and all items in listbox deselected then in the same module place the following code which sheet event code.

Private Sub Worksheet_Activate()
Me.CheckBox1.Value = True
Call CheckBox1_Click
Me.CheckBox1.Value = False
End Sub[/code][/pre]

kaushik03 · Aug 13, 2012

Hi shrivallabha,

Thank you for modifying the code.

I would like to know couple of more things from you:

1)As I told you that I am importing the data from some other sheet into sheet1, the row numbers (from col D to col M) will always vary. Considering that I identify the last row(in col D) and passing the same in the code while defining the range. But then if I select A from listbox (rows with A are populated) and then select Z, rows with Z are not coming up (as the lastrow in second[Z] selection is considered as 19). But before running the listbox macro, if I hardcode another piece of macro to unhide rows say from 11 to 2000, the selection from Z is working perfectly fine. Is there any way we can dynamically do this?

2) I see that, if I place the unique list in some other sheet (say sheet2) and modify the named range accordingly and then passing the same in the code(as you have show under redim statement), I am getting 'Microsoft Visual Basic error 400'). Is this a rule that we have to keep the unique list in the same sheet where we place the listbox? Or can we modify this as well.

3)Say User hit 'deselect all' check box to get the data populated back. After the user select any item from listbox, I want the checkbox to be unmarked (plz see the chk1 macro in sheet1). Once I call this 'chk1' macro in 'RunFilter' subroutine, the screenupdating is not happening properly. Can u tell me the reason why?

I have uploaded the workbook here with the code(and comments) I was trying to modify with some new data:

https://hotfile.com/dl/166714893/a155e5f/ListBox_ide_Unhide.xlsm.html

Looking forward to your help.

Kaushik

shrivallabha · Aug 13, 2012

Why not work out "all" your needs before you post? Please check if this last post of yours covers all your concerns, if not then post back.

kaushik03 · Aug 13, 2012

shrivallabha,

Sorry if I am bothering you by asking questions one after another without consolidating all in one place....my apologies!!

My last post covers my all concerns regarding this ListBox and CheckBox issues.

Thank you for all your help so far...

Regards,

Kaushik

shrivallabha · Aug 13, 2012

It is not about bothering and I didn't mean it rudely. Most of the time we do this without realizing our final goal so it is always advisable to sit down once and analyze the situation and work out final goal (kind of flowsheet). You can make it on a rough paper as well. This reduces the actual design time considerably with "few" changes.

Lets start working with manual part first.

1. Change the "LstDyn" as below to refer 2nd sheet.

=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A,0)-2,1)

The named range in itself is not a problem. In previous code it was referring to Sheet1 so it was not working correctly.

2. Hidden rows always cause a problem when dealing with programmatically as you have already experienced. That said you can always call the last row in the worksheet to rescue. Replace the "Complete" code in Sheet's module with the following:

[pre]

Code:

Sub RunFilter()
Dim boolNone As Boolean
Dim varList() As Variant
Dim rng As Range

Application.ScreenUpdating = False

With Me

&#39;This should take care of variable last row issue
.Range(&#34;D11:D&#34; & .Rows.Count).EntireRow.Hidden = False
&#39;Set Reference to Sheet2 Explicitly
ReDim varList(Sheets(&#34;Sheet2&#34;).Range(&#34;LstDyn&#34;).Count - 1)

For i = 1 To .ListBoxes(&#34;List Box 2&#34;).ListCount
If .ListBoxes(&#34;List Box 2&#34;).Selected(i) Then
boolNone = True
&#39;You don&#39;t need to refer Checkbox all the way there. See next line
.CheckBox1.Value = False
varList(i - 1) = .ListBoxes(&#34;List Box 2&#34;).List(i)
Else
varList(i - 1) = &#34;False&#34;
End If
Next i

If boolNone = False Then MsgBox &#34;No item selected in Listbox!&#34;: Exit Sub

For Each rng In .Range(&#34;D12:D&#34; & .Range(&#34;D&#34; & Rows.Count).End(xlUp).Row)
If Application.IsError(Application.Match(rng.Value, varList, 0)) Then
rng.EntireRow.Hidden = True
End If
Next rng

End With

Application.ScreenUpdating = True

End Sub

Private Sub CheckBox1_Click()
Application.ScreenUpdating = False
With Me
If .CheckBox1.Value = True Then
For i = 1 To .ListBoxes(&#34;List Box 2&#34;).ListCount
.ListBoxes(&#34;List Box 2&#34;).Selected(i) = False
Next i
&#39;Hard coding needs to be removed here as well
.Range(&#34;D12:M&#34; & .Rows.Count).EntireRow.Hidden = False
End If
End With
Application.ScreenUpdating = True
End Sub

[/pre]

3. When you are calling to a sub then settings applied to the application continue to be the same as the parent sub which calls it. So you do not need to set them again. I have removed both smaller subs as they've been taken care of.

4. It was silly of me to not include checkbox un-checking part while setting filter. You will see that included in above code.

Post back if there's concern which needs to be addressed.

kaushik03 · Aug 14, 2012

Hi shrivallabha,

I also, indeed, realized that I should first design the flow of my work before initiating the actual work.Thank you very much for advising me on the same. Going forward, I will definitely follow these instructions which would actually help in saving lot of time.

Regarding your solution, it is absolutely perfect.You have solved all my queries.

I am pretty much in the initial phase of my VBA learning curve and trying to pick up things slowly. But I have been learning a lot from this forum as this forum has some wonderful VBA (EXCEL) experts like you who can guide us in the right way. I truly appreciate your help Shri!!

Regards,

Kaushik

kaushik03 · Aug 14, 2012

Hi shrivallabha,

I noticed only one issue while using your code as follows:

When I select any item ( say 'A')from listbox at the first instance,screen updating is not happening properly(I could see the row hiding action one by one slowly).But later, selecting any further item(s), screen updating is working absolutely fine. However I could see that you have taken care of this part by Application.ScreenUpdating = False and then reset it to Application.ScreenUpdating = True.

May I request you to please address this issue and let me know the reason?

https://hotfile.com/dl/166928477/4662b36/ListBox_ide_Unhide.xlsm.html

Best regards,

Kaushik

Debraj · Aug 14, 2012

Hi Kaushik,

Just do one thing..

Instead of With Me use With Sheets(1)..

At let us know the response..

Please dont ask why.. as I also know Me and ActiveSheet, both are same in the case, but its working..

Regards,

Deb..

kaushik03 · Aug 14, 2012

@Deb,

Yes it is working with your suggested technique.

I also tried by deleting application.screenupdating = true from both the places (listbox and checkbox macro)...and it is working fine. I do not know why but might be screenupdating always true at the end by default(perhaps we were overloading the macro by setting it true again)....not sure....

Thanks for your help dear....

Kaushik

SirJB7 · Aug 14, 2012

Hi, kaushik03!

I've tested RunFilter procedure with Me, Sheets(1) and Sheets("Sheet1"), and I didn't found any difference regarding process times:

14/08/2012 03:05:25 p.m. 14/08/2012 03:05:25 p.m.

14/08/2012 03:05:45 p.m. 14/08/2012 03:05:45 p.m.

14/08/2012 03:05:47 p.m. 14/08/2012 03:05:47 p.m.

14/08/2012 03:05:48 p.m. 14/08/2012 03:05:48 p.m.

14/08/2012 03:05:48 p.m. 14/08/2012 03:05:48 p.m.

14/08/2012 03:05:51 p.m. 14/08/2012 03:05:52 p.m.

14/08/2012 03:05:56 p.m. 14/08/2012 03:05:56 p.m.

It's always been immediate. Only the first time I run it from the downloaded file stored in temp folder I noticed the action of hiding rows one by one, but once I saved it in another (usual) folder, closed Excel, reopened it and load the saved file, it worked smoothly, even first time run a little slower.

So I assume that it isn't an object With-ed issue but an Excel caching issue, that's to say, Excel doesn't have previously allocated the object referenced in With statement and rng (this one is referenced as known by later binding, as opposite to early binding) and that's why it run slower first time as you're forcing to go (allocate & address) to row 1048576 (.Rows.Count for Sheets(1)).

Personally, I'd also like to:

a) use constants for sheet names and refer them as Worksheet(ksSheetName)

b) use a variable to store the upper limit of the For: .Range("D12:D" & .Range("D" & Rows.Count).End(xlUp).Row)

c) use a With rng, within the For, it caches only one the rng object, instead of twice

Hope it helps.

Regards!

shrivallabha · Aug 15, 2012

Hi kaushik,

I have also noticed the screen painting which is a bit of annoyance. Personally, I'd ignore such thing as the results may not be the same at each person's end.

The first significant reason is the "display". The screenupdating as you'll naturally guess is associated with application re-painting the "visible" screenspace with the latest changes applicable. So even if human eye doesn't grasp all the subtle changes, it notices flickers on-screen. So set various zooms and see for yourself the effect of it.

While you have some suggestions put forth to you, I'd like to start with the less obvious one.

Does your actual sheet / workbook have formula especially the one's listed as volatile. Volatile formulae tend to clog up sheet calculations and may not give you a stable performance. Instead of LstDyn's current formula which involves OFFSET (which is volatile) function, lets try with this function.

=Sheet2!$A$2:INDEX(Sheet2!$A$1:$A$100,MATCH("z",Sheet2!$A$1:$A$100,1))

Read about Volatile functions:

http://www.decisionmodels.com/calcsecretsi.htm

Does this improve anything?

HideUnhide data as user selects item(s) from listbox

kaushik03

Member

shrivallabha

Excel Ninja

kaushik03

Member

shrivallabha

Excel Ninja

kaushik03

Member

shrivallabha

Excel Ninja

kaushik03

Member

shrivallabha

Excel Ninja

kaushik03

Member

kaushik03

Member

Debraj

Excel Ninja

kaushik03

Member

SirJB7

Excel Rōnin

shrivallabha

Excel Ninja