Are Option Button Arrays possible in Excel? 1

[mattj63]

Is there a way to find out which option button on a worksheet is selected by looping through an option button array? I remember doing something like this in VB5. I'm trying to do the same in VBA in Excel now but can't make the button names an array. Right now I have to check each button individually for a true value. Works OK with 2 or 3 buttons but with more it gets cumbersome.

[tt]
If OptionButton1 Then
gSystem = 1
ElseIf OptionButton2 Then
gSystem = 2
Else
gSystem = 3
End If
[/tt]

Can't you do something like this?
[tt]
For i = 1 to 3
if optionbutton(i) Then
gSystem = i
end if
Next i
[/tt]

 

[SkipVought]

Sheet controls are SHAPES. They are included in the Sheet Object Shapes Collection

dim shp as shape

for each shp in Sheets("Sheet1").shapes
  with shp
    select case .name
       case "OptionButton1"
          gSystem = 1
       case "OptionButton2"
          gSystem = 2
       case "OptionButton3"
          gSystem = 3
    end select
  end with 
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mattj63]

I'm not sure that helps. You're still referring to each specifically with the select case statement. Can you consider the shapes an array and find the one with that is selected?

maybe something like (syntax may be wrong since I haven't tried in Excel):

for each shp in Sheets("Sheet1").shapes
  if shp.value then
      gSystem = right$(shp.name,1)
  end if
next

 

[SkipVought]

You can do that. However, be aware that there may be other shapes on the sheet. So a better approch might be...

for each shp in Sheets("Sheet1").shapes
  With shp
    if Left(shp.name,12) = "OptionButton" then
      gSystem = mid$(shp.name,13)
    end if
  end with
next

you really should not use right 1, because it is possible to have more than 9 option buttons


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mattj63]

Thanks. Also thanks for the comment about right$. I would have found that but only after the code broke when I added more option buttons

 

[SkipVought]

Always good to get a "yea-but" optinion. ;-)

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]