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. :)
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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,343
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,805
Messages
5,833,773
Members
430,232
Latest member
Testsubject

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top