Drop down bar to hide column according to the content of a cell

[Daphie]

Hi all,

I need help. How can i write a macro such that
when the user select (e.g. 2 ) from the dropdown for, it hides column
G:Z
when the user select (e.g. 5) from the dropdown form, it hides Column
J:Z


Thank you so much.

Cheers,
Daphie

 

[Dave Peterson]

I dropped a combobox from the control toolbox toolbar onto a worksheet.

I assigned a range with all the values I wanted (0-10 for my test) to the
listfillrange.

Then I doubleclicked on that combobox (while in design mode) and used this code:

Option Explicit
Private Sub ComboBox1_Change()
Dim HowManyCols As Long

HowManyCols = Me.ComboBox1.Value

Me.Range("f1:z1").EntireColumn.Hidden = False
If HowManyCols > 0 Then
Me.Range(Me.Range("e1").Offset(0, HowManyCols), "Z1") _
.EntireColumn.Hidden = True
End If
End Sub

 

[Daphie]

Hi... I'm sorry to bother you again...

If the columns to be hidden is in another worksheet. How should I alter
the codes?
And I have a list of 20.
If select 1 will hide O:AZ
If select 2 will hide Q:AZ
If select 3 will hide S:AZ

 

[Dave Peterson]

O, Q, S, ...
skip a column in between?

Option Explicit
Private Sub ComboBox1_Change()

'If select 1 will hide O:AZ
'If select 2 will hide Q:AZ
'If select 3 will hide S:AZ

Dim HowManyCols As Long

HowManyCols = Me.ComboBox1.Value

With Worksheets("Sheet2")
.Range("o1:az1").EntireColumn.Hidden = False
If HowManyCols > 0 Then
.Range(.Range("m1").Offset(0, (2 * HowManyCols)), "aZ1") _
.EntireColumn.Hidden = True
End If
End With
End Sub

 

[Daphie]

Btw, what is "m1" meant for??

And...

My combo box is inserted in excel worksheet, not in the design mode.
If i were to create a userform in the design mode, how do i insert and
place in onto the worksheet??

Thanks a lot

 

[Dave Peterson]

M1 is the cell in column M, row 1--it's just an address.

If you do a little math, then you'll see what this does:

..Range("m1").Offset(0, (2 * HowManyCols))

If you put 1 in the combobox, then Howmany = 1
so this becomes

..Range("m1").Offset(0, (2 * 1))
or
..Range("m1").Offset(0, 2)

..offset(0,2) means stay on the same row, but go two columns to the right.

Two columns to the right of M1 is O1.

And if you used a combobox from the control toolbox toolbar, you'll see that
there's an icon on that control toolbox toolbar that allows you to go into
design mode--to move the control, to double click on it and get to the VBE (to
change the code).

Btw, what is "m1" meant for??

And...

My combo box is inserted in excel worksheet, not in the design mode.
If i were to create a userform in the design mode, how do i insert and
place in onto the worksheet??

Thanks a lot