[RESOLVED] Help with creating option button controls...

[Stogie03]

I am trying to create, at runtime on a user form in excel, an optionbutton for each worksheet in a workbook. But for some reason i am getting an error of "Constant expression required" on 'Dim optionbut(p) As OptionButton'

Does that mean i have to make it a constant? I changed it to "6" to test this and it didnt give an error but then gives me an "Invalid use of the New Keyword" on 'optionbut(i) = New OptionButton'

What am i doing wrong?

Dim p As Integer
    p = Sheets.Count
    Dim optionbut(p) As OptionButton
    
    For i = 0 To Sheet.Count - 1
        optionbut(i) = New OptionButton

[Siddharth Rout]

try this

Private Sub CommandButton1_Click()
    Dim cControl As Control
    Dim p As Integer, j As Integer
    
    p = Sheets.Count
    j = 20
 
    For i = 1 To p
        Set cControl = Me.Controls.Add("Forms.OptionButton.1", "MyCaption", True)
     
        With cControl
            .Caption = "aaa" 'Change it so that it changes after every loop
            .Width = 200
            .Height = 50
            .Top = 20 + j
            .Left = 20
        End With
        
        j = j + 30
    Next
 
End Sub

[Stogie03]

Koolsid, works perfect...thank you very much!

But just for my learning experiance, why was my code/way not the best way to do it?

[Siddharth Rout]

thats because vba doesn't support control array like

Option1(0)
Option1(1)
Option1(2)

you could have achieved the same in vb6 with little modifications...

[Stogie03]

Koolsid,

Ok now how do i loop through all the created optionbuttons to see what one is the selected?

The method im use to using in vb.net isnt working....

For Each Control In Me.Controls
    If (typeof(control) is optionbutton) Then
        If Control.Value = True Then
            sName = Control.Caption
        End If
    End If
Next
Next

[Stogie03]

nevermind, figured out i need to use the TypeName method....

For Each Control In Me.Controls
    If TypeName(Control) = "OptionButton" Then
        If Control.Value = True Then
            sName = Control.Caption
        End If
    End If
Next