Automatic Creation of Radio Button

[pingme89]

I have created a search function within my Workbook. The number of query returns vary.

I would like to know how to make an option button for every query result on the fly.

I would also need to be able manipulate the option button to identify the row of the selected option button.

For example:

Row 1 : A1 = "John"
Row 2 : A2 = "Mary"
Row 3 : A3 = "Roger"

I want to be able to make an option button in say B1, B2, and B3.

If a user selects the Option Button in B2, I want to be able to loop from 1 to 3 and identify which option button was selected.

I would name each Option button: Opt1, Opt2, Opt3 etc..

So I want to be able to loop from 1 to 3 and test if Opt1 = True all the way to Opt = true or not. Opt1 would be on row 1, Opt2 would be on Row 2 etc.

Is this even possible?

For the time being I have made a macro to make option buttons automatically up to 200. I then hid them and only made them visible if there was an entry in the same row.

The problem is I would need to make condition statements for all 200 radio buttons and the code would become extremely long and take a lot of time to write.

This is what I have at the moment:

If Workbooks(wbname).Worksheets("AccessDB").Opt2.Value = True Then
Workbooks(wbname).Worksheets("Settings").Range("A38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("A2").Value
Workbooks(wbname).Worksheets("Settings").Range("B38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("B2").Value
Workbooks(wbname).Worksheets("Settings").Range("C38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("C2").Value
Workbooks(wbname).Worksheets("Settings").Range("D38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("D2").Value
Workbooks(wbname).Worksheets("Settings").Range("E38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("E2").Value
Workbooks(wbname).Worksheets("Settings").Range("F38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("F2").Value
Workbooks(wbname).Worksheets("Settings").Range("G38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("G2").Value
Workbooks(wbname).Worksheets("Settings").Range("H38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("H2").Value
Workbooks(wbname).Worksheets("Settings").Range("I38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("I2").Value
Workbooks(wbname).Worksheets("Settings").Range("J38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("J2").Value
Workbooks(wbname).Worksheets("Settings").Range("K38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("K2").Value
Workbooks(wbname).Worksheets("Settings").Range("L38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("L2").Value
Workbooks(wbname).Worksheets("Settings").Range("M38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("M2").Value
Workbooks(wbname).Worksheets("Settings").Range("N38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("N2").Value
End If
If Workbooks(wbname).Worksheets("AccessDB").Opt3.Value = True Then
Workbooks(wbname).Worksheets("Settings").Range("A38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("A3").Value
Workbooks(wbname).Worksheets("Settings").Range("B38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("B3").Value
Workbooks(wbname).Worksheets("Settings").Range("C38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("C3").Value
Workbooks(wbname).Worksheets("Settings").Range("D38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("D3").Value
Workbooks(wbname).Worksheets("Settings").Range("E38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("E3").Value
Workbooks(wbname).Worksheets("Settings").Range("F38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("F3").Value
Workbooks(wbname).Worksheets("Settings").Range("G38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("G3").Value
Workbooks(wbname).Worksheets("Settings").Range("H38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("H3").Value
Workbooks(wbname).Worksheets("Settings").Range("I38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("I3").Value
Workbooks(wbname).Worksheets("Settings").Range("J38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("J3").Value
Workbooks(wbname).Worksheets("Settings").Range("K38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("K3").Value
Workbooks(wbname).Worksheets("Settings").Range("L38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("L3").Value
Workbooks(wbname).Worksheets("Settings").Range("M38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("M3").Value
Workbooks(wbname).Worksheets("Settings").Range("N38").Value = Workbooks(wbname).Worksheets("AccessDB").Range("N3").Value
End If

As you can see that is only for 2 option buttons. If I had to make them for 200 options I would have to repeat a lot of code with the only difference being the row number on the right hand side of the equation.

 

[CalcSux78]

Not sure if you've ever used ListBoxes with multiple columns before, but would allow for a much simpler code and lightning performance compared to the above method.

Check out this article on Listboxes: Globaliconnect.com

 

[My Aswer Is This]

I think it would be best if you explained what your trying to do in detail.
Using a large number of option buttons and very long scripts is probable not needed.
Maybe we could provide a lot easier way to do this.

 

[pingme89]

I think it would be best if you explained what your trying to do in detail.
Using a large number of option buttons and very long scripts is probable not needed.
Maybe we could provide a lot easier way to do this.

I used an SQL query and the results are displayed in the AccessDB worksheet. I want to make a simple interface for the user to select one of the query results.