Formatting a dynamic userform based on number of dynamic controls

[gravanoc]

Instead of constantly changing the number of userform checkboxes & their captions, I thought it would be a good idea to dynamically create the checkboxes based on some user input in a special range. I do have that part set correctly, so the checkboxes appear with the correct caption, etc., but now I've got to determine an appropriate way to accommodate the Height, Width, Left, & Top of the Userform, a Frame, a the spawned controls.

First problem so far is that when I try to change my userform's height to accommodate more controls, it tells me that this object doesn't support this property or method. I think this is happening because the code is in a normal module, not the userform's module. Regardless, I don't know how to change it as needed, so it's cutting off excess controls right now.

This is the main sub. The calling method determines which userform is assigned to choiceFrm, since there are 12 of them. The userforms are meant to allow for either one choice or multiple, so with the former it will create an option button and the latter makes a checkbox. Besides the error, any other ideas to improve my methodology are welcome.

Option Explicit
Public ctrlCol As Collection

Sub CreateControls(choiceFrm As UserForm, valRng As Range, buttonType As String, formWidth As String)
    
    Dim chkBox As clsChecks
    Dim optBtn As clsOptions
    
    Dim labelLeft As Double, labelTop As Double
    Dim formHeight As Double, frameHeight As Double
    Dim controlCount As Long, i As Long, j As Long
    
    controlCount = valRng.Cells.Count
    labelLeft = choiceFrm.Label1.Left
    labelTop = choiceFrm.Label1.Top

    ' This is mostly guesswork at the moment, but it was starting to look right
    ' Not sure what the difference is between InsideHeight & Height
    If choiceFrm.Frame1.Height < (controlCount * 24) + 40 Then
        If formWidth = "Single" Then
            formHeight = choiceFrm.InsideHeight + ((controlCount - 8) * 25) + 40
            choiceFrm.Height = formHeight ' Causes error
            frameHeight = formHeight + choiceFrm.Frame1.Height
            choiceFrm.Frame1.Height = frameHeight ' This works though
        Else
            ' Not done yet
        End If
    End If
    
    Set ctrlCol = New Collection

    For i = 0 To controlCount - 1
    
        If buttonType = "CBox" Then
        
            Set chkBox = New clsChecks
            With chkBox
            
                Set .cBox = choiceFrm.Frame1.Controls.Add("forms.CheckBox.1")
                With .cBox
                    .AutoSize = True
                    .Caption = valRng.Cells(i + 1, 1).Value
                    .Name = "CB" & i
                    .BackColor = &HC0C0C0
                    .ForeColor = &H80000012
                    .Font = "Tahoma"
                    .Height = 22.5
                    .Width = 125
                    
                    If formWidth = "Single" Then
                        .Left = labelLeft
                        .Top = labelTop + 50 + (24 * (i - 1))
                    Else
                        ' I used j here because I couldnt think of
                        ' a way to use i in a formula that cut it
                        ' in half appropriately
                        .Left = labelLeft + 66
                        .Top = labelTop + 26 * (j - 1)
                        j = j + 1
                    End If
                    
                End With

            End With
        
            ctrlCol.Add Item:=chkBox
        
        Else
        
            Set optBtn = New clsOptions
            
            With optBtn
            
                Set .optButton = choiceFrm.Frame1.Controls.Add("forms.OptionButton.1")
                With .optButton
                    .AutoSize = True
                    .Caption = valRng.Cells(i + 1, 1).Value
                    .Name = "CB" & i
                    .BackColor = &HC0C0C0
                    .ForeColor = &H80000012
                    .Font = "Tahoma"
                    .Height = 24
                    .Width = 125
                    
                    If formWidth = "Single" Then
                        .Left = labelLeft
                        .Top = labelTop + 26 * (i - 1)
                    Else
                        .Left = labelLeft + 66
                        .Top = labelTop + 26 * (j - 1)
                        j = j + 1
                    End If
                    
                End With
            
            End With
            
            ctrlCol.Add Item:=optBtn
            
        End If

    Next
    
End Sub

 

[Domenic]

I see that you've declared choiceFrm as a UserForm.

If you take a look at the UserForm class using the Object Browser (F2), you'll see all of its properties and methods. You'll notice, though, Height is not a property of this class. And so you'll get that error saying that it doesn't support this property or method.

Now, when you insert a new userform, two things happen. It is automatically given a name, such as UserForm1 (or one with the next available number when there is more than one userform), and it creates a class by the same name.

So, if the name of the userform remains unchanged, the name of the class would be UserForm1. But if the name is changed, let's say it's changed to frmData, then the name of the class changes to frmData as well.

So, instead of declaring choiceFrm as a UserForm, you should declare it as either an Object or the actual class of the userform. I believe that the UserForm class is a base class, which is inherited by a newly created userform.

To declare it as an Object...

Sub CreateControls(choiceFrm As Object, valRng As Range, buttonType As String, formWidth As String)

To declare it as the actual class of the userform...

Sub CreateControls(choiceFrm As UserForm1, valRng As Range, buttonType As String, formWidth As String)

Hope this helps!