Error: "Unable to Set Hidden Property Range" for Xls 2007 Users, FIX?

[scherlerd]

Hi All – </SPAN></SPAN>

I’m hoping you can help correct this problem. I have a large workbook that will be delivered to several hundred users and requires many data entry fields, meaning the workbook and each of its worksheets need to be protected. On one of the worksheets, users of Excel 2007 are running into the following run-time error: “Unable to set hidden property of the range class”. I need this to be fixed so that Excel 2007 & 2003 users won’t run into this problem.</SPAN></SPAN>

What: I have two form control option buttons (option button 30 & 31) that the user selects. Each button corresponds to a set of rows where the user is instructed to input additional information. The idea is that when one button is selected, only the rows of pertinent info displays and hides the rows that aren’t needed. More specifically, when option button 30 is selected, rows 41 through 48 should be hidden. When option button 31 is selected, rows 41 through 48 should be displayed.</SPAN></SPAN>

How: Here is the code I’m using. I wrote this on a PC using Excel 2010. The cell “X12” is the linked cell.</SPAN></SPAN>

</SPAN></SPAN>
Sub OptionButton30_Click()</SPAN></SPAN>
If Range("X12").Value = 1 Then</SPAN></SPAN>
Rows("41:48").Select</SPAN></SPAN>
Selection.EntireRow.Hidden = True</SPAN></SPAN>
Else</SPAN></SPAN>
Rows("41:48").Select</SPAN></SPAN>
Selection.EntireRow.Hidden = False</SPAN></SPAN>
End If</SPAN></SPAN>
Range("D16").Select</SPAN></SPAN>
End Sub</SPAN></SPAN>

Sub OptionButton31_Click()</SPAN></SPAN>
If Range("X12").Value = 2 Then</SPAN></SPAN>
Rows("41:48").Select</SPAN></SPAN>
Selection.EntireRow.Hidden = False</SPAN></SPAN>
Else</SPAN></SPAN>
Rows("41:48").Select</SPAN></SPAN>
Selection.EntireRow.Hidden = True</SPAN></SPAN>
End If</SPAN></SPAN>
Range("D16").Select</SPAN></SPAN>
End Sub</SPAN></SPAN>
[/endcode]</SPAN></SPAN>

Why: I want to make this usable for users of Excel 2007 and 2003, however users of 2007 are getting the run-time error: “Unable to set hidden property of the range class” as stated above. It works fine in 2010 and I can’t re-produce the error myself. </SPAN></SPAN>

I’m hoping this will be a simple solution for you excel gurus! Any ideas?!</SPAN></SPAN>

Thanks!</SPAN></SPAN>

 

[Andrew Poulsom]

Like this?

Worksheets("SheetName").Rows("41:48").EntireRow.Hidden = ActiveSheet.OptionButtons(Application.Caller).Value = xlOn

Change "SheetName" to suit.

 

[scherlerd]

Sorry for the late response - I was trying to work this one out on my own. That's what I thought the code would be but I keep getting the same run-time error as before "1004: Unable to set the hidden property of the range class". Nothing else is different. I'm just adding your code above to one of the same command buttons, in the same area, that you helped me with before. Does it need to be in a different location?

 

[scherlerd]

I don't know if this helps but, for some reason, I can substitute a different worksheet (from the one I want to affect) into your code and it works. But when I type in the name of the worksheet I actually want, I get the error. For instance, I want to make this change on a sheet named "Performance" and when I type:

: Worksheets("Performance").Rows("67:77"). EntireRow.Hidden = ActiveSheet.OptionButtons(Application.Caller).Value = xlOn

...

I get an error. But when I enter:

: Worksheets("Purpose").Rows("67:77"). EntireRow.Hidden = ActiveSheet.OptionButtons(Application.Caller).Value = xlOn

...

it works. I guess VBA can't seem to set the "performance" sheet as an object?

 

[Andrew Poulsom]

Is the offending worksheet protected?