Hide Columns based on a cell value

[Tami]

I have a spreadsheet that i want to hide columns a:H if the value in cell
L1="N" and unhide if theres a "Y". i'd prefer for the "macro" to be
real-time based on when then N or Y changes.
i got this code off the internet but it doesn't work consistently...i should
have know better than to deviate from this faithful website

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("L1").Value = "N" Then
Columns("A:H").EntireColumn.Hidden = True
Else
Columns("A:H").EntireColumn.Hidden = False
End If
End Sub

anyone?.....

 

[Tami]

possibly the macro does not work below because its case senstive...can i
enter a lower or uppercase "y"

 

[Don Guillett]

Just delete the part about selection and restrict to cell L1

Private Sub Worksheet_change(ByVal Target As Range)
if target.address <> Range("L1").address then exit sub
if ucase(target) = "N" Then
Columns("A:H").Hidden = True
Else
Columns("A:H").Hidden = False
End If
End Sub

 

[Tami]

works like a charm....forgot to ask, if i protec the worksheet because i have
a lot of users, will it still work?

what about hiding rows base on a value in column c....for example i'll use
column A to indicate "S" if i want to print all the spring styles, "U" for
all the summers syles but if i put "B" i want that row to appear on both
reports...

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Tami]

Now i have two sets of columns that the user may need to hide.
In the first case below IF L2=Y then hide columns A:H
Now i'd like to add:
IF BQ1=Y, hide columns BK:BN
can the macro ask two questions?

Current Code:
Private Sub Worksheet_change(ByVal Target As Range)
If Target.Address <> Range("L1").Address Then Exit Sub
Me.Unprotect Password:="paspas"
If UCase(Target) = "N" Then
Columns("A:H").Hidden = True
Else
Columns("A:H").Hidden = False
End If
Me.Protect Password:="paspas"
End Sub

 

[Don Guillett]

Answered off list

 

[LKP]

I'm curious...what was the answer to this? I want to do something similar to
what Tami is doing (hiding different columns based on the values in different
cells).

Thanks!

 

[Don Guillett]

More info?

 

[LKP]

I have a sheet with columns of modules on the top and different volumes on
the side which will fill in with numbers is that certain module is selected
on a different sheet. If the module is not selected, the cells in the row
under that module will be zero. I want to hide any columns of the modules
that were not selected on the other sheet and therefore have zeros in their
columns. Right now I have cells set up to sum the columns. If the sum of
that column equals zero, I want that column hidden. There are 7 modules and
7 columns. Is there a way to hide the ones that are not being used?

Thanks!

 

[Don Guillett]

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.