Link to home
Start Free TrialLog in
Avatar of Harsh Kumar
Harsh KumarFlag for Denmark

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?
Avatar of Bill Prew
Bill Prew

What will be specified in the listbox?

~bp
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.
Avatar of Harsh Kumar

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
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
@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
Avatar of Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj)
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Open in new window