Hiding rows by a logic formula

A

Anton

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
 
I

Iain King

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.
Click to expand...

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
 
A

Anton

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-----


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
Click to expand...
("ExtraRows").EntireRow.Hidden = false
else
Sheets("MySheet").Range
Click to expand...
("ExtraRows").EntireRow.Hidden = true
 
I

Iain King

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?
Click to expand...


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
 
I

Iain King

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
 
Top