Hiding Rows with VBA...

A

anton

Hello,

I am a newbie to the programing side of Excel and would
like to ues 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.

I have used the following code...

Private Sub MechCheck_Change()
If MechCheck.Value Then
Sheets("Main").Range("ExtraRows").EntireRow.Hidden
= False
Else
Sheets("Main").Range("ExtraRows").EntireRow.Hidden
= True
End If
End Sub

with MechCheck the name of my checkbox, Main being the
sheet name and ExtraRows the name of rows to be hidden.

When I check the box in excel, my first problem is that I
get an error which indicates the macro can not be found.
Secondly, within the VB editor the second line is
highlighted yellow and it has a run time error '424' and
says Object Required.

Can anyone point me in the direction of my error?

many thanks!
-Anton
 
M

Mike Fogleman

First get rid of the Forms checkbox and use the checkbox from the Control
Toolbox menu. Click the Triangle icon at the left end of the Control Toolbox
menubar. This will put you in 'Design Mode' for the checkbox object.
Right-click on the checkbox and select Properties. Edit the name to say
MechCheck. Further down you can change the caption that displays beside the
checkbox with something like 'Calculation Option'. Close the Properties
window, right-click again on the checkbox and select Code. There should be
the start of a macro there for you like:
Private Sub MechCheck_Click()
End Sub

Put this code there:

Private Sub MechCheck_Click()
If MechCheck.Value = True Then
Range("ExtraRows").EntireRow.Hidden = False
Else
Range("ExtraRows").EntireRow.Hidden = True
End If
End Sub

Close the code window, click the Triangle icon to leave design mode. Your
check box should work as planned.

Mike F
 
A

Alex J

A couple little suggestions....

I like to specify the sheet, and the "if" statement can be made simpler:

Private Sub MechCheck_Click()
Sheets("Main").Range("ExtraRows").EntireRow.Hidden = Not MechCheck.Value
End Sub

Alex J
 
M

Mike Fogleman

The If statement is true enough, but sometimes difficult for a Newbie to
follow. The named range is at the workbook level, so sheetname is not
necessary, but good practice for readability.
 
Top