Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows by a logic formula
Hello...
I am using a check box (forms version) to give a second option for a standard calcultion sheet. Under the first option it requires just one row. For the second it would require an additional 3 rows. I would like to hide the additional 3 rows if the default calc is being made. So they would be unhiden under once the check box is check and the second calculation is being perfomed. Can anyone help me out on how to coordinate this action. Thanks for your insight! -Anton |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows by a logic formula
I am using a check box (forms version) to give a second option for a standard calcultion sheet. Under the first option it requires just one row. For the second it would require an additional 3 rows. I would like to hide the additional 3 rows if the default calc is being made. So they would be unhiden under once the check box is check and the second calculation is being perfomed. Can anyone help me out on how to coordinate this action. you have to enter code into the CheckBox's Change event: assuming your sheet is called MySheet, checkbox is MyCheckbox. you have to select the rows you want to hide/unhide and Define a name for them ("ExtraRows") private sub MyCheckBox_Change() if MyCheckBox.Value then 'checkbox is ticked Sheets("MySheet").Range("ExtraRows").EntireRow.Hid den = false else Sheets("MySheet").Range("ExtraRows").EntireRow.Hid den = true endif end sub Iain King |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows by a logic formula
Iain,
Thanks for your helpful reply! I tried to enter the code as you suggested and have encountered a run time error 'object required' The second line of code is highlighted, which leads me to believe that I do not have the name of the check box correct...? It seems silly, but I could not find a way to check its name, I just recall what was written in the text when I first inserted it. Could this be my problem? I have named the rows that I would like hidden "ExtraRows" like you suggested and my sheet name is "Main". Here is what I have in the code... Private Sub CheckBox31_Change() If CheckBox31.Value Then 'checkbox is ticked Sheets("Main").Range("ExtraRows").EntireRow.Hidden = False Else Sheets("Main").Range("ExtraRows").EntireRow.Hidden = True End If End Sub This is in a module, not a class module. Does that matter? Thanks! Anton -----Original Message----- I am using a check box (forms version) to give a second option for a standard calcultion sheet. Under the first option it requires just one row. For the second it would require an additional 3 rows. I would like to hide the additional 3 rows if the default calc is being made. So they would be unhiden under once the check box is check and the second calculation is being perfomed. Can anyone help me out on how to coordinate this action. you have to enter code into the CheckBox's Change event: assuming your sheet is called MySheet, checkbox is MyCheckbox. you have to select the rows you want to hide/unhide and Define a name for them ("ExtraRows") private sub MyCheckBox_Change() if MyCheckBox.Value then 'checkbox is ticked Sheets("MySheet").Range ("ExtraRows").EntireRow.Hidden = false else Sheets("MySheet").Range ("ExtraRows").EntireRow.Hidden = true endif end sub Iain King . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows by a logic formula
I tried to enter the code as you suggested and have
encountered a run time error 'object required' The second line of code is highlighted, which leads me to believe that I do not have the name of the check box correct...? It seems silly, but I could not find a way to check its name, I just recall what was written in the text when I first inserted it. Could this be my problem? I have named the rows that I would like hidden "ExtraRows" like you suggested and my sheet name is "Main". Here is what I have in the code... Private Sub CheckBox31_Change() If CheckBox31.Value Then 'checkbox is ticked Sheets("Main").Range("ExtraRows").EntireRow.Hidden = False Else Sheets("Main").Range("ExtraRows").EntireRow.Hidden = True End If End Sub This is in a module, not a class module. Does that matter? Sorry - didn't realise that controls placed on a worksheet behave differently than controls placed on a userform. So: Go to the visual basic editor (tools-macro-visual basic editor), double click on the sheet your control is in. add: Sub ShowHideRows() If CheckBox31.Value Then 'checkbox is ticked Sheets("Main").Range("ExtraRows").EntireRow.Hidden = False Else Sheets("Main").Range("ExtraRows").EntireRow.Hidden = True End If End Sub Close the visual basic editor. On your sheet, right click the checkbox - Assign Macro... assign it the above macro. Iain |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows by a logic formula
Ack - and again :(
I've been trying to work out how to refer to a checkbox on a worksheet, without success. I added a checkbox to a blank sheet, then tried to refer to it through Sheet.OLEObjects, but it wasn't there. Anyway, unless someone else can tell you how to do this, I suggest you use the value held in the linked cell (which I've named 'CheckBoxLinked'): Sub ShowHideRows() If Sheets("Main").Range("CheckBoxLinked").Value Then 'checkbox is ticked Sheets("Main").Range("ExtraRows").EntireRow.Hidden = False Else Sheets("Main").Range("ExtraRows").EntireRow.Hidden = True End If End Sub Iain |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Letting users calculate Excel forms on the web while hiding the logic | Excel Discussion (Misc queries) | |||
Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
print area selection slows excel, formula for hiding rows?? | Excel Discussion (Misc queries) | |||
hiding rows with a formula | Excel Discussion (Misc queries) | |||
Hiding rows from a formula | Excel Worksheet Functions |