running code from a cell value

[heritagedoorchris]

I have searched the site and am unable to find a solution. I have two groups of options buttons created from the forms toolbar. There are 2 options in one group and 5 options in the other. Based upon the value concatenation of these two control cells I am running if/or/then/elseif code to hide and unhide certain rows of data. Essentially my code is unprotecting the sheet, turning off screen update, performing 4 if then statements, ending the IF, reapplying screen update and reprotecting the sheet. I have tried running this as a Private Worksheet Subroutine: Calculate, SelectionChange, and Change. I can not get this to hide my rows. I really would not like to link these to Control box option buttons since I would need 10 of them to accomplish this task. Any suggestions would be greatly appreciated.

 

[XLGibbs]

Try putting your code in a standard module and call the code on the option click buttons...you would assign the same button_Click macro to each option button if necessary..

Or you could post the code for review...

Have you stepped through the code using f8 or breakpoints to see why it fails?

 

[heritagedoorchris]

Thank you for the quick reply. In brief, this is a sample of the code. D2 contains a formula which concatenates the controls of the option box groups. The hang up is an error stating Unable to Set the Hidden Property of the Range class. The error occurs on a different line each time depending on the value of D2. I am going to try your suggestion of calling the code from the option click buttons. By a standard module do you mean a worksheet module (right click the XL at top left of window)? Please excuse my inexperience and lack of knowledge and thank you for the help.

If Range("D2") = 11 Or Range("D2") = 12 Then
Rows("15:16").EntireRow.Hidden = False
Rows("17:24").EntireRow.Hidden = True
ElseIf Range("D2") = 13 Or Range("D2") = 14 Or Range("D2") = 15 Then


Chris

 

[Norie]

Chris

Where do you currently have this code?

Does the worksheet have any protection?

 

[heritagedoorchris]

The code is in a sheet module (right click the sheet tab). The sheet is protected by I unprotect the sheet and turn off updating before i run the if statements. AT the end of the routine I turn on updating and reprotect the worksheet.

Chris

 

[XLGibbs]

Change hidden to visible and see if that works.

EntireRow.Visible = False

 

[heritagedoorchris]

Thank you. I tried Visible but received an error #438 - Object does not support this property or method. Maybe that is my fault for not mentioning that I am Windows XP Pro, Excel 2003 SP2 and VB6.3. Anyway, I finally got it to work as a worksheet_selection change although not the way I wanted it to work. After selecting the option buttons the code will not run until i select any cell on the worksheet causing the sheet to calculate and the code to run. Is there a way to force the calculation in the code?

Thanks so much to Norie and Gibbs. You folks always make me think. Some day I hope to be able to help others as you have me.