running code from a cell value

heritagedoorchris

New Member
Joined
Jul 6, 2005
Messages
16
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. :)
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

XLGibbs

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
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

New Member
Joined
Jul 6, 2005
Messages
16
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,059
Office Version
  1. 365
Platform
  1. Windows
Chris

Where do you currently have this code?

Does the worksheet have any protection?
 

heritagedoorchris

New Member
Joined
Jul 6, 2005
Messages
16

ADVERTISEMENT

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

Well-known Member
Joined
Feb 25, 2005
Messages
2,446
Change hidden to visible and see if that works.

EntireRow.Visible = False
 

heritagedoorchris

New Member
Joined
Jul 6, 2005
Messages
16
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,522
Messages
5,572,633
Members
412,475
Latest member
JaredNAU
Top