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!!!

 

[Fluff]

Hi & welcome to MrExcel.
How about

For i = LBound(OptionList) To UBound(OptionList)
   If j = 33 Then
      c = c + 210
      j = 1
   Else
      j = j + 1
   End If
   Set opt = Me.Controls.Add("Forms.OptionButton.1", "radioBtn" & i, True)
   opt.Caption = OptionList(i)
   opt.Top = opt.Height * j + 20
   opt.Left = c
   opt.GroupName = "Options"
   opt.Width = 200
   Me.Width = 750
   Me.Height = 700
Next
   With Me.Controls.Add("forms.label.1", "Label1")
      .Caption = "Fluff"
      .Top = 5
      .Width = 750
      .TextAlign = fmTextAlignCenter
   End With
  
End Sub

 

[Barboza Babcock]

The code above worked like a charm!!!! Thank you so much. I did modify the code to create check boxes as I will need to select more than one compound.

I do have a new problem. Solve one problem, and move on to the next. As I said, I am no coder. I have absolutely no clue as to how I can find and refer to those boxes that get checked. I've been struggling with this for hours.

The form shows just fine. I've added a command button to the form. When I click on the command button, I would like to be able to find the boxes that have been checked and be able to show the captions in a msgbox. I can't figure out how to refer to the checked boxes.

When I have created other forms, I named the checkboxes. For instance ckBoxAccetone, ckBoxBenzene etc.. I know what they are called. The boxes created for this form are created on the fly. What are they named?

Here is the code I have written that I know will show how clueless I am, but will hopefully show what I want to do.

For intA = 1 To 125

If Me.Controls.CheckBox.Value = True Then
   MsgBox Me.Controls.CheckBox.Caption
   MsgBox Me.Controls.CheckBox.Name
End If

Next intA

As always, any help is greatly appreciated.

THX!!!

 

[Fluff]

If you change the part of the code that adds the checkboxes to

Me.Controls.Add("Forms.Checkbox.1", "ChkBx" & i, True)

then you can use

Private Sub CommandButton1_Click()
   Dim Ctrl As Control
   Dim Msg As String
   
   For Each Ctrl In Me.Controls
      Debug.Print Ctrl.Name
      If Ctrl.Name Like "ChkBx*" Then
         If Ctrl Then Msg = Msg & vbLf & Ctrl.Caption
      End If
   Next
   MsgBox Msg
End Sub

 

[Barboza Babcock]

Once again. Thank you.

I ended up doing this:

Private Sub cmdContinue_Click()
Dim ctrl As Control
frmCpds.Hide
intA = 1
intB = 1
Erase strCustomCpds
Do Until Me.Controls("ckBox" & intA).Caption = ""
   If Me.Controls("ckBox" & intA).Value = True Then
      strCustomCpds(intB) = Me.Controls("ckBox" & intA).Caption
      intB = intB + 1
   End If
intA = intA + 1
Loop
End Sub

Does just what we need. Got the checked items into the array. It took me a while to realize the name of the check boxes were ckBox1, 2,3 ...

This is the line that confused me:

Set opt = Me.Controls.Add("Forms.OptionButton.1", "radioBtn" & i, True)

Once I got a msgbox to pop up the names, I was good to go. Your code helped a lot.

As above, much appreciated. Back to partying on the 4th. Just wanted to get back to you and express my appreciation! Have a great 4th! Thx

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Barboza Babcock]

I'm at the very end of this project. I'm at the usability part now. If we go to create a second custom report, the check boxes that I had checked off in the previous report start as checked for the new report.

Basically, I want to set all the check box values as false every time the form is generated. I tried the following, and locked up Excel. I have a talent for that.

In any event, here is the code that locks up. Definitely not elegant. Brute force coding, that looks like it should do what I want. But it doesn't. And of course it crashes excel at the same time.

intA = 1
strA = "ckBox" & Trim(Str(intA)) & ".Caption"
Do Until strA = ""
   strA = "ckBox" & Trim(Str(intA)) & ".Value"
   strA = False
   MsgBox strA
   intA = intA + 1
   strA = "ckBox" & Trim(Str(intA)) & ".Caption"
Loop

I did try opt.value = false when generating the check boxes. That didn't work either.

Other than initializing all the check boxes as unchecked, all is good.

Thx

 

[Fluff]

Whenever the userform is initialised & the checkboxes created they will be unchecked, so not quite sure what you're saying.

 

[Barboza Babcock]

I am under the same impression.

We have one main menu that we start off with. One of the buttons is for reports. The report form allows us to enter a lab id and the method used for analysis. There is a checkbox on the report from for "Custom Reports". If we check the box, and hit the "Continue" button, the form you have helped us with (frmCpds) opens and displays all the check boxes. We check off which compounds we want on the report,and hit the "Continue" button on the frmCpds form.

At this point, the report is generated. We then go back to the report form and enter the lab id for another sample to be reported. This time when frmCpds loads, the check boxes that were checked for the previous report are already checked.

We do not "unload" the form between creating reports. The form is just hidden (frmCpds.hide).

After some thought I'm guessing that I need to unload the form prior to showing it. Yes? No?

  If IsLoaded(frmCpds) Then
    Unload frmCpds
 End If

If I am correct, I need to unload the frmCpds form prior to using it to generate reports after the first report.??? Something like the above(which didn't work).

 

[Fluff]

Ok, if you are not unloading it, then try

Private Sub UserForm_Activate()
   Dim i As Long
   For i = 1 To 130
      Me.Controls("ChkBx" & i).Value = False
   Next i
End Sub