I have 500 rows of numbers each with an option button beside it. I
need Excel to recognize a certain cell when a certain option button is
selected. I'd like to do it with out entering in 500 different lines
of code. Below is my code. I need the R17C3 to be a variable and
change with which option button is selected. So the option button will
need to be a variable also. Any idea on how or if I can pull this
off?


Private Sub OptionButton1_Click()
Union(Sheets("READY MIX").Range("C13:C60"), Sheets("READY
MIX").Range("F13:F60"), Sheets("READY MIX").Range("I13:I60"),
Sheets("READY MIX").Range("L13:L60"), Sheets("READY
MIX").Range("O13:O60"), Sheets("READY MIX").Range("R13:R60"),
Sheets("READY MIX").Range("U13:U60"), Sheets("READY
MIX").Range("X13:X60")).FormulaR1C1 = _
"='READY MIX-MAIN'!RC+('READY MIX-MAIN'!RC*(1-(LF!
R23C3/100)))*(LF!R17C3)/100"
End Sub

Thanks,
Keith

Here is the best approach. The option button has a linked cell property
which will put either true or false in a cell. Make the linked cells in one
column. Then make a worksheet change that looks for changes in the column
where the true or false is located. You will only need one macro to handle
all the cases.

"Keith" wrote:

I have 500 rows of numbers each with an option button beside it. I
need Excel to recognize a certain cell when a certain option button is
selected. I'd like to do it with out entering in 500 different lines
of code. Below is my code. I need the R17C3 to be a variable and
change with which option button is selected. So the option button will
need to be a variable also. Any idea on how or if I can pull this
off?


Private Sub OptionButton1_Click()
Union(Sheets("READY MIX").Range("C13:C60"), Sheets("READY
MIX").Range("F13:F60"), Sheets("READY MIX").Range("I13:I60"),
Sheets("READY MIX").Range("L13:L60"), Sheets("READY
MIX").Range("O13:O60"), Sheets("READY MIX").Range("R13:R60"),
Sheets("READY MIX").Range("U13:U60"), Sheets("READY
MIX").Range("X13:X60")).FormulaR1C1 = _
"='READY MIX-MAIN'!RC+('READY MIX-MAIN'!RC*(1-(LF!
R23C3/100)))*(LF!R17C3)/100"
End Sub

Thanks,
Keith

Joel,

I think you're on to something. I'm working on setting up a test run
now.

Thanks,
Keith