Selecting a control based on cell information

[Jono]

Hello,

I have 40 option boxes in a list, beside information in cells (in excel). I want to be able to select a control (ie Control27) based on information in the cell beside it (would be cell N27 for instance), but I haven't found a way to do that. Can anyone help please?

[albertw]

Hello,

I have 40 option boxes in a list, beside information in cells (in excel). I want to be able to select a control (ie Control27) based on information in the cell beside it (would be cell N27 for instance), but I haven't found a way to do that. Can anyone help please?

hi
can you rephrase your question as it not clear what you want to accomplish.
as i read it:
you want to select an optionbox based on information in a cell beside it?
what information ? a number ?
get the cell's contents, use this as an index
(hope you have your optionboxes in an array, otherwise you may forget the story)
then,
OptionBox(Number).Value=1

[Jono]

I think what I really need to be doing is getting my checkboxes in an array. How do I do this? I have 40 and I want to be able to iterate through the whole set, but currently I have to refer to each individually... Any ideas?

Ie my code so far would be as follows...

With ActiveSheet
.Option1.Value = False
.Option2.Value = False
.Option3.Value = False
(and on and on until 40...)
End With

Please help!

[albertw]

I think what I really need to be doing is getting my checkboxes in an array. How do I do this? I have 40 and I want to be able to iterate through the whole set, but currently I have to refer to each individually... Any ideas?

Ie my code so far would be as follows...

With ActiveSheet
.Option1.Value = False
.Option2.Value = False
.Option3.Value = False
(and on and on until 40...)
End With

Please help!

hi

sry, took some time to figure things out.
mistaken vba for vb6 where you can use Index as arraycounter
maybe next thing is some use to you
no need to rename all your optionbutton names - taken into account that ALL of them start with "OptionButton" followed by a number.

 
Private Sub SetOptionButton()
Arg = Range("a1").Value
ButtonName = "OptionButton" & Trim(Str(Arg))
ActiveSheet.OLEObjects(ButtonName).Object.Value = True
End Sub
 
Private Sub ResetOptionButton()
For Arg = 1 To 40
ButtonName = "OptionButton" & Trim(Str(Arg))
ActiveSheet.OLEObjects(ButtonName).Object.Value = False
Next Arg
End Sub
 
 

[Jono]

Thank you!! Your idea led to me eliminating 220 lines of code!! Geez that sure is cleaner now. Anyways, in case anyone else can benefit from this in the future, here is what I ended up with. Far from perfect, but hey, I'm just starting..

Dim Loc As String
Loc = ActiveSheet.Range("Q7").Value
Dim Incr As Integer
Incr = 1

Do While Incr < 41
If Worksheets("Attachments").Range("B" & Loc).Value = "D" Then
OptionName = "Option" & Trim(str(Incr))
ActiveSheet.OLEObjects(OptionName).Object.Value = True
End If
Incr = Incr + 1
Loc = Loc + 1
Loop