Hide Multiple Columns Using Form ComboBox

Rockey_01

New Member
Joined
Sep 19, 2014
Messages
21
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
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)
Code:
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

New Member
Joined
Sep 19, 2014
Messages
21
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,384
Messages
5,595,862
Members
414,027
Latest member
zippyfrog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top