VBA Code to arrange 100 plus option buttons on the screen, or allow scrolling

[Barboza Babcock]

We have adapted some vba code to programmatically populate a form with 130 option buttons. We would like to arrange the options in 4 columns, rather than having to scroll to get to the last few options.
We would also like to be able to add a label at the top of the form without it being overwritten by option buttons.

Private Sub UserForm_Initialize()


Dim OptionList(1 To 130) As String, intA As Integer, strTest(1), s As Variant, i As Integer
Dim opt As Control


 'Get compound list
     strTest(1) = "524"
     Sheets("Tests").Select
     Range("A2").Select
     Select Case strTest(1)
        Case Is = "524"
           Cells.Find(What:="524 Cpds", After:=ActiveCell, LookIn:=xlFormulas2, _
              LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
              MatchCase:=True, SearchFormat:=False).Activate
             
       ActiveCell.Offset(rowoffset:=1).Select
     
     intA = 1
     Do Until ActiveCell = ""
        OptionList(intA) = ActiveCell.Text
        intA = intA + 1
        ActiveCell.Offset(rowoffset:=1).Select
     Loop
     
     End Select

For Each s In OptionList
        Set opt = frmCpds.Controls.Add("Forms.OptionButton.1", "radioBtn" & i, True)
        opt.Caption = s
        opt.Top = opt.Height * i
        opt.GroupName = "Options"
        opt.Width = 200
        frmCpds.Width = 750
        frmCpds.Height = opt.Height * (i + 2)

        i = i + 1
Next

End Sub

Apologizing in advance for my lack of coding skills.

Many thanks to all who take the time to look!!!

 

[Barboza Babcock]

PERFECT!!!!!!!!!!!!!!!!!!!!!!!!!!! Project complete. This was a weekend thrash. For over 30 years we have been generating reports in a specified form. This code, and your help have allowed us to create reports for selected compounds "on the fly".

Excellent. I can't tell you how much I appreciate your help. I have a "how to" macro in excel. All commented out. It tells me how to do a bunch of different things in excel. It's my tool chest. You have helped me to add some great new techniques to the tool chest. I'm new to the forum. Is there a way I can give you "stars".

TY,

BB

 

[Fluff]

Glad to help & thanks for the feedback.

Letting me know my idea9s0 have worked & a word of thanks are all I ask for. Which you have already done.