Option buttons

I am looking to assign the caption for my Option Buttons to equal the value of a cell for multiple cells.

An example of a single cell is:

ActiveSheet.OptionButton1.Caption = ActiveSheet.Cells(1, 1).Value

The above code works, but I am looking to use a loop since I am doing several iterations. I want to do this for about 50 option buttons, but I cannot figure out how to develop the syntax for the loop. The idea behind what I want to do is:

For i=i to 50

ActiveSheet."OptionButton" & i.Caption = ActiveSheet.Cells(i, 1).Value

next i

So I would like to have the caption of OptionButton# as whatever is in Cell(#,1)
Anyone have a clue how to do this?

Nick's picture

you can't do that..have a

you can't do that.. have a play with this:
For i=i to 50
ActiveSheet.Shapes(i).Caption = ActiveSheet.Cells(i, 1).Value
next i

Need your opinion

Hello, Nick and Watlinsd.

Nick, I need your opinion for the following subroutine:

 - first one is if the Option Button is a Form Controls:

' ************************* ' ************************* '

Sub LoopFormControls()

Dim oShape As Shape
Dim n As Long

n = 1
For Each oShape In ActiveSheet.Shapes
    If oShape.Type = msoFormControl Then
        If oShape.FormControlType = xlOptionButton Then
            oShape.OLEFormat.Object.Caption = ActiveSheet.Cells(n, 1)
            n = n + 1
        End If
    End If
Next oShape

End Sub

' ************************* ' ************************* '


 - second one is if the Option Button is an ActiveX Controls:

' ************************* ' ************************* '

Sub LoopActiveXControls()

Dim oControl As OLEObject
Dim n As Long

n = 1
For Each oControl In ActiveSheet.OLEObjects
    If oControl.progID = "Forms.OptionButton.1" Then
        oControl.Object.Caption = ActiveSheet.Cells(n, 1)
        n = n + 1
    End If
Next oControl

End Sub

' ************************* ' ************************* '


Best regards.

Nick's picture

nice, Manny... did you try

nice, Manny...

did you try this:

If oControl.progID = "Forms.OptionButton.1" Then

If oControl.progID = "Forms.OptionButton." & n Then

RE: Did you try

Hi, Nick,

I didn't try it, because "Forms.OptionButton.1" is a some kind of constant to identify control objects. I learn for that when, some time ago, I try to dynamically insert controls in my user form, and then for Controls.Add method I should to use the ProgID. And in Excel Help there is a list with that constant:

CheckBox Forms.CheckBox.1
ComboBox Forms.ComboBox.1
CommandButton Forms.CommandButton.1
Frame Forms.Frame.1
Image Forms.Image.1
Label Forms.Label.1
ListBox Forms.ListBox.1
MultiPage Forms.MultiPage.1
OptionButton Forms.OptionButton.1
ScrollBar Forms.ScrollBar.1
SpinButton Forms.SpinButton.1
TabStrip Forms.TabStrip.1
TextBox Forms.TextBox.1
ToggleButton Forms.ToggleButton.1


I don't know what this ".1" at the end means, but by now all works fine.


Thanks and best regards.

Vishesh's picture

Thanks Manny & Nick for

Thanks Manny & Nick for sharing this info in ProgID