| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Hide Columns based on a cell value
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?..... |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide Columns based on a cell value
possibly the macro does not work below because its case senstive...can i
enter a lower or uppercase "y" "Tami" wrote: 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?..... |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide Columns based on a cell value
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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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?..... |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide Columns based on a cell value
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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?..... |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide Columns based on a cell value
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" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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?..... |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide Columns based on a cell value
Answered off list -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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?..... |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide Columns based on a cell value
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" wrote: Answered off list -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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?..... |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide Columns based on a cell value
More info?
-- Don Guillett Microsoft MVP Excel SalesAid Software "LKP" wrote in message ... 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" wrote: Answered off list -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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?..... |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide Columns based on a cell value
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" wrote: More info? -- Don Guillett Microsoft MVP Excel SalesAid Software "LKP" wrote in message ... 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" wrote: Answered off list -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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?..... |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Hide Columns based on a cell value
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "LKP" wrote in message ... 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" wrote: More info? -- Don Guillett Microsoft MVP Excel SalesAid Software "LKP" wrote in message ... 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" wrote: Answered off list -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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" wrote: 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 -- Don Guillett Microsoft MVP Excel SalesAid Software "Tami" wrote in message ... 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?..... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Writing a macro to hide columns based on cell value | Excel Discussion (Misc queries) | |||
| Automatic Hide group of a columns based on cel value | Excel Discussion (Misc queries) | |||
| Is there a way to hide worksheets and/or rows/columns based on information enter into a particular cell of range of cells? | New Users to Excel | |||
| How do I automatically hide columns in a worksheet based on a cell value? | Excel Worksheet Functions | |||
| Is there a way to HIDE a row based on a value of a cell ? | Excel Discussion (Misc queries) | |||
Linear Mode
