Auto Hiding Columns

F

Fester

I want to auto hide a specific number of columns depending on a
validation list selection.

IE, user selects a shop ID and columns D, E, F, G, are hidden because
there are 3 people in the shop.

I created a button to accomplish this, but I want to automate as much
as possible for ease of use.

Is this possible?

Fester
 
D

Debra Dalgleish

You could use event code to hide the columns. For example, with a data
validation list in cell A2 (with numbers 1-9):

'===============================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Target.Value > 0 Then
Range(Cells(1, 1), Cells(1, Target.Value)) _
.EntireColumn.Hidden = False
Range(Cells(1, Target.Value + 1), Cells(1, 10)) _
.EntireColumn.Hidden = True
End If
End If
End Sub
'===============================

To use this code, right-click on the sheet tab, and choose View Code.
Copy and paste the code onto the sheet's module, where the cursor is
blinking.
 
B

bscarano

OK, I'm not getting it.

The first two columns are frozen, the next columns (C-R) contain the
person's name, and a # sign (alternating).

Depending on the value in the cell (A2 in this case), I want to hide
specific columns.

If the value is 3, then columns I thru R are hidden.
If the value is 6, then colums O thru R are hidden.

Fester
 
W

Wolf

In said:
OK, I'm not getting it.
Click to expand...
The first two columns are frozen, the next
columns (C-R) contain the person's name,
and a # sign (alternating).
Click to expand...
Depending on the value in the cell (A2 in
this case), I want to hide specific columns.
Click to expand...
If the value is 3, then columns I thru R are hidden.
If the value is 6, then colums O thru R are hidden.
Click to expand...

Try this

Again, right-click on the sheet tab, choose View Code, and copy
and paste the following code onto the sheet's module.

--

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Target.Value = 3 Then
ActiveCell.Offset(0, 7).Range("A1:J1") _
.EntireColumn.Hidden = True
ElseIf Target.Value = 6 Then
ActiveCell.Offset(0, 13).Range("A1:D1") _
.EntireColumn.Hidden = True
Else
ActiveCell.Offset(0, 6).Range("A1:p1") _
.EntireColumn.Hidden = False
End If
End If
End Sub

--

You can add additional elseif's if needed. The final
else (rather obviously) unhides the hidden columns.

Hope this is what you were looking for.

Wolf

--
*****************************************
Dolor ad tempus est.
Sanatur vulni.
Cicatrices amantur a scortillis.
*****************************************
Life is too short to suffer fools gladly.
*****************************************
You can have Freedom or Peace,
Don't EVER count on having both.
 
Top