Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows and Columns
I have two check boxes.
Depending on which is checked, I have a cell that reads either 1 or 2. Right now, I have conditional formatting set up to "Black Out" sections of the workbook, depending on which box is checked. Is there any way to just hide these rows or columns? The blacked out thing looks kinda tacky. Example: Cell=1, then hide rows 2,3,and 4. Cell=2, then hide rows 8, 9, and 10. Something like that. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows and Columns
Smarty,
Try something like this, go to VBE, from the Microsoft Excel Objects under your Workbook, select the sheet you want to work with, then paste in this code: Private Sub Worksheet_Change(ByVal Target As Range) If Left(Target.Address, 2) = "$A" Then If Target.Value = "1" Then Rows("2:4").EntireRow.Hidden = True ElseIf Target.Value = "2" Then Rows("8:10").EntireRow.Hidden = True ElseIf Target.Value = "" Then Cells.Select Selection.EntireRow.Hidden = False End If End If End Sub Let me know if this is what you were looking for. Andrew Armstrong |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows and Columns
How are you blacking out, conditional formatting?
You could try event code, like so Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H10" '<=== change to suit Const COLUMNS_1 As String = "M:O" '<=== change to suit Const COLUMNS_2 As String = "T:Z" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Me.Columns(COLUMNS_1).Hidden = .Value = 1 Me.Columns(COLUMNS_2).Hidden = .Value = 2 End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SmartyPants" wrote in message ups.com... I have two check boxes. Depending on which is checked, I have a cell that reads either 1 or 2. Right now, I have conditional formatting set up to "Black Out" sections of the workbook, depending on which box is checked. Is there any way to just hide these rows or columns? The blacked out thing looks kinda tacky. Example: Cell=1, then hide rows 2,3,and 4. Cell=2, then hide rows 8, 9, and 10. Something like that. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows and Columns
Oops, I see you said CF. Doh!
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SmartyPants" wrote in message ups.com... I have two check boxes. Depending on which is checked, I have a cell that reads either 1 or 2. Right now, I have conditional formatting set up to "Black Out" sections of the workbook, depending on which box is checked. Is there any way to just hide these rows or columns? The blacked out thing looks kinda tacky. Example: Cell=1, then hide rows 2,3,and 4. Cell=2, then hide rows 8, 9, and 10. Something like that. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows and Columns
I did it like this Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SHIFT As Variant SHIFT = Sheets("Production").Range("DN28").Value If SHIFT = 1 Then Sheets("Production").Rows("37:38").EntireRow.Hidde n = True Sheets("Production").Rows("34:36").EntireRow.Hidde n = False ElseIf SHIFT = 2 Then Sheets("Production").Rows("34:36").EntireRow.Hidde n = True Sheets("Production").Rows("37:38").EntireRow.Hidde n = False End If |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows and Columns
Surely DN38 is Target, so why not just check target?
and no need to set entirerow when addressing rows., And you can do the hide/unhide in one statement as I showed Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = "$DN$38" Then Me.Rows("37:38").Hidden = Target.Value = 1 Me.Rows("34:36").Hidden = Target.Value = 2 End If End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "SmartyPants" wrote in message ps.com... I did it like this Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim SHIFT As Variant SHIFT = Sheets("Production").Range("DN28").Value If SHIFT = 1 Then Sheets("Production").Rows("37:38").EntireRow.Hidde n = True Sheets("Production").Rows("34:36").EntireRow.Hidde n = False ElseIf SHIFT = 2 Then Sheets("Production").Rows("34:36").EntireRow.Hidde n = True Sheets("Production").Rows("37:38").EntireRow.Hidde n = False End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hiding rows and columns in XL 07 | New Users to Excel | |||
hiding columns with merged rows | Excel Discussion (Misc queries) | |||
Hiding rows and columns in a procedure | Excel Programming | |||
Hiding rows and columns | Excel Programming | |||
Hiding of rows and columns | Excel Discussion (Misc queries) |