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]

Welcome to MrExcel.

Are those ActiveX CommandButtons? Where have you put your VBA code?

 

[scherlerd]

Thanks, Andrew!

No, they are form control Option Buttons. The excel code is on Sheet 2 under the MS Excel Object folder when I open up VBA. Does that answer your question?

 

[Andrew Poulsom]

If the code is in the module for a worksheet they are ActiveX CommandButtons. This simplified version of your code works for me in Excel 2007:

Sub OptionButton30_Click()
    Rows("41:48").EntireRow.Hidden = OptionButton30.Value = True
End Sub

Sub OptionButton31_Click()
    Rows("41:48").EntireRow.Hidden = OptionButton31.Value = False
End Sub

 

[scherlerd]

I see. I'm still learning vba and how to trouble shoot issues!

So I swapped in your simplified code and am now getting a "run time error 424: Object required". Did I read/input something wrong into vba? Or do I need to write a new macro with your code and re-assign it to my command buttons?

Thanks again for your help!

 

[Andrew Poulsom]

Are you absolutely certain that your VBA code is in a Worksheet module? That can't be the case for an OptionButton from the Form Controls. Assigned macros must be in a General module like Module1.

 

[scherlerd]

Okay. So I went back and looked, and these two codes are in Module14. Sorry for the confusion! Does that mean that these are actually ActiveX Controls (and likely the reason for the error for 2007 users)? Even though I inserted Option Buttons from the Form Control option?

If so, how do I replace them with Form Control buttons so that the code you sent above will work?

 

[Andrew Poulsom]

In that case they are Form Control OptionButtons. Try:

Sub OptionButton30_Click()
    Rows("41:48").EntireRow.Hidden = ActiveSheet.OptionButtons(Application.Caller).Value = xlOn
End Sub

Sub OptionButton31_Click()
    Rows("41:48").EntireRow.Hidden = ActiveSheet.OptionButtons(Application.Caller).Value = xlOff
End Sub

which works for me in Excel 2007.

 

[scherlerd]

Hazzah! It worked! Thanks so much for your due diligence and speedy help! I'm not quite sure what your code is telling excel to do/why this is solution works and mine didn't but happy to know that it does. If you get a second and wouldn't mind offering a quick explanation that would be helpful to learn for the future. If youdon't have the time, I understand!

Thanks again!!

 

[scherlerd]

Hi Andrew - sorry to have another ask. But do you know a quick way to use the same command buttons to achieve the same affect on another sheet? So when the user clicks Option Button 31, it hides active rows 41:48 AND rows on another Worksheet different from the active one? I've tried a bunch of different options but keep getting a "object required" error message. Thought you might know a simple solution!

Thanks!