Hiding rows after a check box is selected

[sprlvlyldy]

First and formost this site ROCKS! I am familiar with the basics of Excel, but not with how creative you can really be and I am learning a lot from this site! So Thank you!!


Long story short, I am creating a "form" that will be completed and routed for signature. The problem is, when manually completed, they are usually incorrect. So, to help the "form filler outers," I am trying to create something in Excel. What I need to do is, I have 3 rows with check boxes in place selected from Form Controls:


1) Product Specific (yes checkbox in Cell B7, N/A checkbox in cell C7)

2) List Values (yes checkbox in Cell B8, N/A checkbox in cell C8)

3) Manufacturing Facility/Customer Information (yes checkbox in Cell B8, N/A checkbox in cell C8).


And when either/or box is selected, I would like certain rows to unhide/hide.

so in this case,for the first selection, 1)..., I would like to hide rows 11-44 in the worksheet if N/A is selected.

for the second choice, if N/A is selected, hide rows 46-51

and the third, hide rows 53-75 if N/A is selected.

and then when it is printed, the hidden cells would not print.


Is this possible?


Thank you!! =)

sprlvlyldy

 

[vaskov17]

the code below assumes you have a check box called "Check Box 1" in "Sheet1" and hides rows 11 to 44 based on its value. You can create a Sub for each checkbox by right clicking on it, selecting "Assign Macro", then select "New".


Sub CheckBox1_Click()

Application.ScreenUpdating = False

Dim iCount As Integer

Dim iStart As Integer

Dim iEnd As Integer


iStart = 11

iEnd = 44


If Sheet1.Shapes("Check Box 1").OLEFormat.Object.Value = Checked Then

For iCount = iStart To iEnd

Rows(iCount).EntireRow.Hidden = True

Next iCount

Else

For iCount = iStart To iEnd

Rows(iCount).EntireRow.Hidden = False

Next iCount

End If

Application.ScreenUpdating = True

End Sub

 

[sprlvlyldy]

Thank you for your response, unfortunately I am not very familiar yet with creating macros, and when I pasted this in to create the macro, I get an error that says:


Compile Error

Method or data member not found


and when I click OK, the word Shapes in If Sheet1.Shapes is highlighted in blue.

Any suggestions how to correct this?


Thanks!

sprlvlyldy

 

[Elesario]

I know how it goes with inexperienced users not putting the right information into the right place on excel based forms.


The only way you can get the rows to hide would be using a macro to respond to your checkboxes. An alternative non-macro option would be to use conditional formatting.


Try defining a conditional format linked to the outputs of the checkboxes which when they are in the N/A mode changes colours of the foreground and background of the cells you want the user to ignore to the same colour (i.e. white on white). This will create an empty space which your users will (hopefully) ignore.


The downside is that if the areas covered are large then you'll end up with large empty spaces on any printouts.


As for the macro provided by vaskov17, I suspect that you don't have a 'Sheet1'. Try replacing it with 'ActiveSheet' instead, as the active sheet will always be the same as the one the checkbox is on.

 

[sprlvlyldy]

Awesome it finally worked!!!! Thank you for your help!!