Hiding rows and/or colums by using drop-down-lists (multiple selections)

[Exceluser2014]

Dear all,

For my job I've to create a new user-friendly template for a lot people who no only the basic of Excel.

How is it possible to hide columns or rows with a drop down list?
I've a drop down list linked to cell A3. So select the first option in the list, then A3 will be 1, select the second option, then A3 will be 2, etc. etc.
For example A3 = 2, Rows 7-18 and 20-28 and columns G-H and I-J should be hidden.

And what if I use multiple selections in a drop down list, how can I hide rows and columns in that case?

It's not relevant if the solution is with VBA or not.

Hope that anyone can help me with it.

Thanks in advance!

 

[pbornemeier]

There are several things that can be considered a "drop down list". How did you put the one you are using in your worksheet?
Are you able to select multiple items with it?
When you make a new selection, should any previous selections be reset? Or will the selections be cumulative?
Is there a reset all option?

 

[pbornemeier]

If you are using a Forms Control List Box, the following should work. Put this code on the worksheet that contains the List Box. Change the List Box reference to match the name of your List Box.

Option Explicit

Sub ProcessListBox()
    'Trigger this sub with button or worksheet selection change
    
    Dim lListIndex As Integer
    
    ShowAllRows
    With ActiveSheet.Shapes("List Box 2").OLEFormat.Object
        For lListIndex = 1 To .ListCount
            If .Selected(lListIndex) Then
                HideSpecifiedRowsAndColumns .List(lListIndex)
            End If
        Next
    End With
    
End Sub

Sub HideSpecifiedRowsAndColumns(lInput As Long)

    With ActiveSheet
    
        Select Case lInput
        Case 1
        Case 2
            .Range("7:18,20:28").EntireRow.Hidden = True
            .Range("G:H,I:J").EntireColumn.Hidden = True
        Case 3
        End Select
    End With
    
End Sub

Sub ShowAllRows()

    With ActiveSheet
        .Columns(1).EntireRow.Hidden = False
        .Rows(1).EntireColumn.Hidden = False
    End With
    
End Sub

 

[Exceluser2014]

Hi,

Thanks for you answer. But I it doesn't work for now.
I've made an example excel sheet. Is it possible to send it?