OptionButton

[jedymaster]

Hi there!

I'm trying to get the status of some OptionButton on a worksheet. At runtime, the OptionButtons seems to be empty but there are several option button on the sheet.

The code I'm using is following :

...
  For Each o In Worksheets("TheSheet").OptionButtons
  If o.Name = obId Then
        getOptionBoxValue = o.Value
        Exit For
    End If
  Next
...

What am i doing wrong ?

Thanx !

Jedymaster

 

[TonyJollans]

Hi Jedymaster,

How have you created your Option Buttons? From the Control Toolbox or the Forms Toolbar? The OptionButtons collection only picks up those created from the Forms toolbar and, also, only exists for backward compatibility and it is best not to use it. You should use the Shapes collection (which will get both types) or the OLEObjects collection (which will get only those created with the control toolbox).

Enjoy,
Tony

 

[jedymaster]

Hi Tony!

The option buttons were added from the form toolbar... Since part of the code I'm using is obselete(as you mentionned), I will use the shape collection instead.

Any good links to get me going with shape collection ?

Thanx a lot!

Jedymaster

 

[TonyJollans]

Hi Jedymaster,

Afraid I don't know any particular links for shapes but

http://www.mvps.org

is always a good place to start - some excellent stuff available.

I rely heavily on the Help system and trying things out - especially trying things out - you get instant feedback - not always understandable, I'll admit - but instant

Enjoy,
Tony

 

[Zathras]

Jedymaster: As Tony has stated, buttons from the Forms toolbar are a bit out of date, but a progrmmer has to do what a programmer has to do...

Here is a short routine that you can use to experiment with and hopefully adapt to meet your needs. It will write a table of Option Buttons on Sheet1 with their values in columns E and F, starting with row 2:
[blue]

Option Explicit

Sub test()
Const LIST_COLNAME = 5

[green]

' Column "E"

[/color]

Const LIST_COLVALUE = 6

[green]

' Column "F"

[/color]

Const LIST_FIRSTROW = 2
Dim nRow As Long
Dim sh As Shape

  nRow = LIST_FIRSTROW
  With Worksheets("Sheet1")
    .Cells(nRow, LIST_COLNAME) = "'Button"
    .Cells(nRow, LIST_COLVALUE) = "'Value"
    nRow = nRow + 1
    For Each sh In .Shapes
      If sh.Type = msoFormControl Then
        If sh.Name Like "Option Button*" Then
          .Cells(nRow, LIST_COLNAME) = sh.AlternativeText
          If sh.ControlFormat = 1 Then
            .Cells(nRow, LIST_COLVALUE) = True
          Else
            .Cells(nRow, LIST_COLVALUE) = False
          End If
          nRow = nRow + 1
        End If
      End If
    Next sh
    .Cells(nRow, LIST_COLNAME) = "'==========="
    .Cells(nRow, LIST_COLVALUE) = "'====="
  End With
End Sub

[/color]