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 wrote:
>
> 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

[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 wrote:
> 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 wrote:
> >
> > 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

[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 wrote:
>
> 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 wrote:
> > 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 wrote:
> > >
> > > 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

--

Dave Peterson

[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 wrote:
> 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 wrote:
> >
> > 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 wrote:
> > > 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 wrote:
> > > >
> > > > 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
>
> --
>
> Dave Peterson

[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).


Daphie wrote:
>
> 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 wrote:
> > 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 wrote:
> > >
> > > 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 wrote:
> > > > 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 wrote:
> > > > >
> > > > > 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson