Hide Multiple Columns Using Form ComboBox

[Rockey_01]

Hi,

I currently doing a project using excel and facing a problem having the form combo box list selection item to hide the sheet column which I need. I found a few thread in this forum but I just can't get it work (run) despite following the code and of course change it to my value.

Basically, I have a list of product for selection and most product have the same column require to be hidden.
Some product require to hide more than 8 different column example: ("A:B:C:D:K:L:M:N:O")

I have created a validation drop down list in cell "B4" and also created a form combobox to link the product list and cell link ("B4") to it. Now, I just need to write a correct code in the combo box assign marco to get it run.

Can anyone help? Many Thanks In Advance

 

[mikerickson]

If B4 is the linked cell for the ComboBox, you don't need to put validation on it. In fact this code doesn't need the combo box to be linked to a cell, although it does need to have an Input Range.

"A:B:C:D:K:L:M:N:O" is not a valid Excel address, but if that is what the user selects when they want those 9 columns hidden, you could use code like this. This code will un-hide previously hidden columns. If one of the entries is not column letters with a colon delimiter (e.g. "none"), the code will un-hide the whole sheet.
Note that this must be called by the DropDown, if it is called from VBA, it will error. (Application.Caller can be replaced with the control's name if calling from VBA is a possibility)

Sub DropDown1_Change()
    Dim strAddress As String
    
    With ActiveSheet
        With .Shapes(Application.Caller).ControlFormat
            strAddress = .List(.ListIndex)
        End With
    
        strAddress = Replace(strAddress, ":", "1,") & "1"
        .UsedRange.EntireColumn.Hidden = False
        On Error Resume Next
        .Range(strAddress).EntireColumn.Hidden = True
        On Error GoTo 0
    End With
End Sub

 

[Rockey_01]

Hi Mikerickson,


Wow, this is harder than i thought. Sorry as i'm not really strong at VB code.


In my combo box, there are 15 types of products list and a total of 30 columns data in the sheet.
From the 15 types of products, assuming that:
7 products will have the same require columns to be hidden (e.g 15 columns)
5 products will have the same require columns to be hidden (e.g 10 columns)
2 products will have the same require columns to be hidden (e.g 25 columns)



If i will to select the drop down within these products it maintain the require numbers of columns to be hidden.
Also i have created 1 product list named as "overiew" meaning to say if i select overview it will give me the entire 30 columns without hidden.
Is there a way to do it? Many Thanks In Advance





From the internet, i saw a code as below (i have change some value) can you advice if i could write a code similar to this?

Range("B4").Select
If ActiveCell.Text = "Product 1" Or ActiveCell.Text = "Product 4" Or ActiveCell.Text = "Product 5" Or ActiveCell.Text = "Product 7" Then
Columns("A:B:C:D:K:L:M:N:O").EntireColumn.Hidden = True
Else
Columns("A:B:C:D:K:L:M:N:O").EntireColumn.Hidden = False

If ActiveCell.Text = "Product 2" Or ActiveCell.Text = "Product 3" Or ActiveCell.Text = "Product 6" Then
Columns("A:B:C:D:E:F:G:K:L:M:N:O,P:Q").EntireColumn.Hidden = True
Else
Columns("A:B:C:D:E:F:G:K:L:M:N:O,P:Q").EntireColumn.Hidden = False



End If
End If
End Sub
PS: I have actually tried the above internet given code in marco and VBA none of it work or run.

 

[mikerickson]

Somewhere, you need a list of which columns match with which products.