Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows with VBA...
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows with VBA...
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 the 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 "anton" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows with VBA...
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 "Mike Fogleman" wrote in message news:UH50d.14819$D%.1312@attbi_s51... 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 the 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 "anton" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding Rows with VBA...
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. "Alex J" wrote in message ... 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 "Mike Fogleman" wrote in message news:UH50d.14819$D%.1312@attbi_s51... 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 the 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 "anton" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Color alternate rows when after hiding selected rows | Excel Worksheet Functions | |||
Hiding Specific Rows Based on Values in Other Rows | Excel Worksheet Functions | |||
Hiding a button when hiding rows | Excel Discussion (Misc queries) | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Copying Rows when hiding other rows | Excel Worksheet Functions |