Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible in Excel to have the result of a formula hide rows.
I was wondering if it was possible to have the result of a formula hide or
unhide rows. Also, is it possible to use a checkbox in excel where if the box is checked off then a row is unhidden and if the box remains unchecked a row is hidden? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible in Excel to have the result of a formula hide rows.
hi
formulas return values, they cannot perform actions like hide and unhide rows. but you can hide and unhide rows(and columns) with a check box. drop a check box on the sheet from the tool box. right click the check box and click view code. paste this code in the code window. Private Sub checkbox1_Click() If Rows("10:13").Hidden = True Then Rows("10:13").Hidden = False checkbox1.BackColor = RGB(0, 0, 255)'change the color too checkbox1.Caption = "hiden" 'set the captions too Else If Rows("10:13").Hidden = False Then Rows("10:13").Hidden = True checkbox1.BackColor = RGB(245, 30, 5) checkbox1.Caption = "unhiden" End If End If End Sub set the rows to your liking. edit the check box name to what ever you set it too. regards FSt1 "NeedHelp" wrote: I was wondering if it was possible to have the result of a formula hide or unhide rows. Also, is it possible to use a checkbox in excel where if the box is checked off then a row is unhidden and if the box remains unchecked a row is hidden? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it possible in Excel to have the result of a formula hide rows.
#1. Nope. Formulas return values to the cells that hold the formulas. They
can't do things like hide/unhide rows--or assign values to other cells. #2. Yep. You could put a checkbox from the Forms toolbar on your worksheet and assign it a macro like: Option Explicit Sub testme() With ActiveSheet If .CheckBoxes(Application.Caller).Value = xlOn Then .Range("17:23").EntireRow.Hidden = True Else .Range("17:23").EntireRow.Hidden = False End If End With End Sub or just a little easier to type: Option Explicit Sub testme() With ActiveSheet .Range("17:23").EntireRow.Hidden _ = CBool(.CheckBoxes(Application.Caller).Value = xlOn) End With End Sub I'm not sure what "checked off" means <vbg. You may need to use xloff instead of xlon. Other things you could do... Data|Filter|autofilter if there's a value in a field that can be used. An event macro (worksheet_Calculate) that would monitor worksheet calculations and if the criteria were met, something would happen. NeedHelp wrote: I was wondering if it was possible to have the result of a formula hide or unhide rows. Also, is it possible to use a checkbox in excel where if the box is checked off then a row is unhidden and if the box remains unchecked a row is hidden? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide rows if nil result of formula in specific column | Excel Discussion (Misc queries) | |||
Hide a formula result until cell has been filled | Excel Discussion (Misc queries) | |||
Hide Rows Based on Cell Result | Excel Programming | |||
Hide #VALUE! in result of array formula | Excel Worksheet Functions | |||
Hide rows if formula result is zero | Excel Programming |