Hide colums based on dropdown list

[mgronov - ExcelForums.com]

I like to be able to hide/unhide colums based on a dropdown lis
selection
The 1st column starts in K and the 7th column in Q

If dropdown selection = 1, then hide columns L to Q, show columns
If dropdown selection = 2, then hide columns M to Q, show columns K-
Etc til
If dropdown selection = 2, then show columns K to

Is there a way to do this in VBA. I've been trying to do this bu
without any success
Any help is appreciated

Michae

 

[David Adamson]

Michael,

Adapt the following
------
Private Sub Selection_Change()
'selection.value , 'selection = the dropdown boxes name

Select Case Selection.Value

Case 1
Worksheets("Data").Range("a1:c1").EntireColumn.Hidden = True
Worksheets("Data").Range("d1:e1").EntireColumn.Hidden = False

Case 2
Worksheets("Data").Range("a1:c1").EntireColumn.Hidden = False
Worksheets("Data").Range("d1:e1").EntireColumn.Hidden = True

Case 3

End Select
End Sub

 

[mgronov - ExcelForums.com]

David
I've modified your code. But I get the [quote:66f5bed38c
Object Required[/quote:66f5bed38c] error message. and it already stop
at Select Cas
DropDown125.Valu
The name of the dropdown is DropDown12
This is the first time I'm trying VBA so sorry for an
stupid questions

[code:1:66f5bed38c]Sub DropDown125_Change(
Select Case DropDown125.Valu

Case
Worksheets("Data").Range("l1:q1").EntireColumn.Hidde
= Tru
Worksheets("Data").Range("k1:k1").EntireColumn.Hidde
= Fals

Case
Worksheets("Data").Range("k1:l1").EntireColumn.Hidde
= Fals
Worksheets("Data").Range("m1:q1").EntireColumn.Hidde
= Tru

Case
Worksheets("Data").Range("k1:m1").EntireColumn.Hidde
= Fals
Worksheets("Data").Range("n1:q1").EntireColumn.Hidde
= Tru

Case
Worksheets("Data").Range("k1:n1").EntireColumn.Hidde
= Fals
Worksheets("Data").Range("o1:q1").EntireColumn.Hidde
= Tru

Case
Worksheets("Data").Range("k11").EntireColumn.Hidde
= Fals
Worksheets("Data").Range("p1:q1").EntireColumn.Hidde
= Tru

Case
Worksheets("Data").Range("k11").EntireColumn.Hidde
= Fals
Worksheets("Data").Range("r1:q1").EntireColumn.Hidde
= Tru

Case
Worksheets("Data").Range("k1:q1").EntireColumn.Hidde
= Fals

End Selec
End Su
[/code:1:66f5bed38c][/i

 

[David Adamson]

Okay the problem is probably this.

I ran my code from a userform and you are probably using a dropdown box on a
worksheet

This is what you need to change to set the object

-------------------
Sub DropDown125_Change()
Dim r As Double

With Worksheets("Data")

r = .DropDowns("drop down 125").Value
End With

Select Case r
Case 1
'code as you have

End
---------------

Hope this solves the problem

David,
I've modified your code. But I get the [quote:66f5bed38c]
Object Required[/quote:66f5bed38c] error message. and it already stops

 

[mgronov - ExcelForums.com]

David
You rock! It works great
Thanks

Michae