Home » Forums » Open Source Learning Center » Question and Answer

Option buttons

Submitted by watlinsd on 13 August, 2012 - 23:26

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?

‹ Regarding click event of charts IF FUNCTION ›
»
Nick's picture

you can't do that..have a

Submitted by Nick on 14 August, 2012 - 08:56.
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

Submitted by manny on 14 August, 2012 - 10:14.

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

Submitted by Nick on 14 August, 2012 - 11:00.

nice, Manny...

did you try this:

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

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

»

RE: Did you try

Submitted by manny on 14 August, 2012 - 12:41.

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

Submitted by Vishesh on 14 August, 2012 - 15:46.

Thanks Manny & Nick for sharing this info in ProgID

»