Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding columns based on cell contents
I am looking for a solution to hide row or columns in a worksheet based upon
a value in a cell of another worksheet. To be more specific, I have a "setup" worksheet, with a column if entires. I have a second worksheet, "checklist" that I would like to format according to the entries in "setup". If setup:A1 is empty, I want to hide columns checklist 1-3 If setup:A2 is empty, I want to hide columns checklist 4-6. ANy ideas how to set this up automatically ? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding columns based on cell contents
Put this in the SetUp worksheet's code window...
Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A2")) Is Nothing Then Exit Sub Worksheets("CheckList").Columns(1).Resize(, 3).Hidden = _ (Len(Range("A1").Value) = 0) Worksheets("CheckList").Columns(4).Resize(, 3).Hidden = _ (Len(Range("A2").Value) = 0) End Sub -- Rick (MVP - Excel) "Bill H" wrote in message ... I am looking for a solution to hide row or columns in a worksheet based upon a value in a cell of another worksheet. To be more specific, I have a "setup" worksheet, with a column if entires. I have a second worksheet, "checklist" that I would like to format according to the entries in "setup". If setup:A1 is empty, I want to hide columns checklist 1-3 If setup:A2 is empty, I want to hide columns checklist 4-6. ANy ideas how to set this up automatically ? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding columns based on cell contents
Rick,
I was not able to get this to work, so I tried a dumbed down version which still didn't do anything: Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("sheet4").Row(3).Hidden = True Worksheets("sheet4").Row(4).Hidden = True End Sub Actually, this gave me a runtime error. Any suggestions ? Thanks Bill H "Rick Rothstein" wrote: Put this in the SetUp worksheet's code window... Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A2")) Is Nothing Then Exit Sub Worksheets("CheckList").Columns(1).Resize(, 3).Hidden = _ (Len(Range("A1").Value) = 0) Worksheets("CheckList").Columns(4).Resize(, 3).Hidden = _ (Len(Range("A2").Value) = 0) End Sub -- Rick (MVP - Excel) "Bill H" wrote in message ... I am looking for a solution to hide row or columns in a worksheet based upon a value in a cell of another worksheet. To be more specific, I have a "setup" worksheet, with a column if entires. I have a second worksheet, "checklist" that I would like to format according to the entries in "setup". If setup:A1 is empty, I want to hide columns checklist 1-3 If setup:A2 is empty, I want to hide columns checklist 4-6. ANy ideas how to set this up automatically ? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding columns based on cell contents
Your dumbed down version looks like you used different criteria than you
posted in your first message. You said your two sheets were named "SetUp" and "CheckList", but your dumbed down version refers to Sheet4; and your original message mentioned A1 and A2, but I'm not sure what Row(3) and Row(4) are for (you were trying to hide columns, not rows). The code I posted does work (I tested it before I posted it) provided the sheets are named "SetUp" and "CheckList" (case is not important) and you are checking the contents of A1 and A2 on the "SetUp" sheet and the code is placed in the "SetUp" sheet's code window. -- Rick (MVP - Excel) "Bill H" wrote in message ... Rick, I was not able to get this to work, so I tried a dumbed down version which still didn't do anything: Private Sub Worksheet_Change(ByVal Target As Range) Worksheets("sheet4").Row(3).Hidden = True Worksheets("sheet4").Row(4).Hidden = True End Sub Actually, this gave me a runtime error. Any suggestions ? Thanks Bill H "Rick Rothstein" wrote: Put this in the SetUp worksheet's code window... Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A2")) Is Nothing Then Exit Sub Worksheets("CheckList").Columns(1).Resize(, 3).Hidden = _ (Len(Range("A1").Value) = 0) Worksheets("CheckList").Columns(4).Resize(, 3).Hidden = _ (Len(Range("A2").Value) = 0) End Sub -- Rick (MVP - Excel) "Bill H" wrote in message ... I am looking for a solution to hide row or columns in a worksheet based upon a value in a cell of another worksheet. To be more specific, I have a "setup" worksheet, with a column if entires. I have a second worksheet, "checklist" that I would like to format according to the entries in "setup". If setup:A1 is empty, I want to hide columns checklist 1-3 If setup:A2 is empty, I want to hide columns checklist 4-6. ANy ideas how to set this up automatically ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding rows or columns based on cell contents | Excel Discussion (Misc queries) | |||
Hiding columns based on row 4 criteria | Excel Worksheet Functions | |||
Hiding columns based on value | Excel Programming | |||
hiding rows based on cell contents | Excel Discussion (Misc queries) | |||
Hiding Columns based on value from another worksheet | Excel Programming |