Harsh Kumar
asked on
VBA - Excel, Hide/unhide range of rows on sheet with listbox selection
Hi guys,
I'm trying to figur out how to hide/unhide rows on a sheet from a listbox selection.
Is this possible?
I'm trying to figur out how to hide/unhide rows on a sheet from a listbox selection.
Is this possible?
Are you aware of the Grouping facility???
Rows or columns can be grouped so that the top row or leftmost column can be used as a summary with the details rows/columns hidden. Settings can be changed so that it is the bottom row or rightmost column of a group.
The hiding/unhiding of the groups can be controlled individually or all at once with buttons in the margin to the left of row numbers or above the column headers.
Rows or columns can be grouped so that the top row or leftmost column can be used as a summary with the details rows/columns hidden. Settings can be changed so that it is the bottom row or rightmost column of a group.
The hiding/unhiding of the groups can be controlled individually or all at once with buttons in the margin to the left of row numbers or above the column headers.
ASKER
@BP - Thank you for you prompt reply, I'm attaching a sample file where i have tried to explain what I'm thinking to do.
@Rob - Yes i'm aware of the grouping functionality but its a "system" im builing so this is how it needs to be.
sample1.xlsx
In short what I was thinking:
I have a Product sheet with 7 listbox's and each listbox have a number of product listed. Each listbox = to a sheet. Each product in the listbox = a range of rows.
So in short if rng_1 in the listbox is ticked (true) = unhide range of rows (called rng_1) in the Data sheet, or if the rng_1 in the listbox is unticked (false) = hide range of rows (called rng_1) in the Data sheet,
I hope this made sense, otherwise please ask
@Rob - Yes i'm aware of the grouping functionality but its a "system" im builing so this is how it needs to be.
sample1.xlsx
In short what I was thinking:
I have a Product sheet with 7 listbox's and each listbox have a number of product listed. Each listbox = to a sheet. Each product in the listbox = a range of rows.
So in short if rng_1 in the listbox is ticked (true) = unhide range of rows (called rng_1) in the Data sheet, or if the rng_1 in the listbox is unticked (false) = hide range of rows (called rng_1) in the Data sheet,
I hope this made sense, otherwise please ask
Hi Hakum,
Please find attached. Toggle with ticking for all 3 range. Instead of ListBox, I am using CheckBox, which does the same thing as you like.
Hide-Unhide-Rows-on-Check-Box_V2.xlsm
Please find attached. Toggle with ticking for all 3 range. Instead of ListBox, I am using CheckBox, which does the same thing as you like.
Hide-Unhide-Rows-on-Check-Box_V2.xlsm
ASKER
@Shums - Thank you for your reply, I have already made it with check boxes and it works for me but if I need to make it with a listbox.?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Neeraj ji! Thank you soo much!! this was exactly what i needed!!!!
You're welcome Hakum! Glad it worked.
Slightly shortened code - this code toggles the Range visibility without the IF statement to check.
Option Explicit
Private Sub ListBox1_Change()
Dim oWS As Worksheet
Dim iX As Long
Set oWS = Sheets("Data")
With Me.ListBox1
For iX = 0 To .ListCount - 1
oWS.Range(.List(iX)).EntireRow.Hidden = .Selected(iX)
Next iX
End With
End Sub
~bp