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
enter a lower or uppercase "y"
Don Guillett
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"Tami" <Ta...@discussions.microsoft.com> wrote in message
news:842D851E-C73C-4AFD...@microsoft.com...
Tami
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
a clear explanation of what you want and before/after examples.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"Tami" <Ta...@discussions.microsoft.com> wrote in message
news:0E361272-B157-4334...@microsoft.com...
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
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"Tami" <Ta...@discussions.microsoft.com> wrote in message
LKP
what Tami is doing (hiding different columns based on the values in different
cells).
Thanks!
Don Guillett
news:C7DD43B2-5407-4F77...@microsoft.com...
LKP
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
a clear explanation of what you want and before/after examples.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com
"LKP" <L...@discussions.microsoft.com> wrote in message
news:A5842AF5-CBBB-4138...@microsoft.com...
Станислав Константинович Терентьев
Please take a look:
https://docs.google.com/spreadsheets/d/1oBjUtvmyQn94YVFnvJVbMCVQe40vZ-D8t8SlgGmsaFs/edit?usp=sharing
The whole sheet is designed to have board game stats written down and summed up, but since the number of players may vary, I'd like to hide columns in the right with no names in the first row by default, and only show a column next to the last one I enter a player's name in the first row. To define whether or not unhide a column on the right from the current one, I suppose to enter a blanc space after entering each player's name, until it is time to enter the last player's game, so once it is, I don't add blank space after it, and the script recognizes it as a signal to not unhide the next column. Is that workable?