Populating tab on multipage control with VBA

[WatfordKev]

I'm looking to populate a tab on a multipage control with a series of checkboxes (although the control is irrelevant). but I don't know how to do it, despite experimenting and looking around for a solution. The nearest I've got is the following which puts the controls on the main form when the tab is clicked, but not on the multipage control tab itself!


Private Sub MultiPage1_Change()

Dim ws As Worksheet
Set ws = Worksheets("Params")

If Me.MultiPage1.Value = 2 Then

Dim cServiceA As Range
For Each cServiceA In ws.Range("Service_Areas")
' CODE HERE
Next cServiceA

End If

End Sub

 

[Andrew Poulsom]

I don't see any code that adds controls there.

 

[Norie]

How about an alternative?

You could add a listbox in design mode and populate that with the captions you were going to use for the checkboxes.

It's properties can be set so that each item has a checkbox next to it.

Private Sub MultiPage1_Change()

    If Me.MultiPage1.Value = 2 Then
    
        With Me.ListBox1
 
            .List = Range("Service_Areas").Value
            ' these can be set at design time
            .ListStyle = fmListStyleOption
            .MultiSelect = fmMultiSelectMulti
            
        End With
    End If
End Sub

 

[WatfordKev]

Apologies - here's the full code, I omitted it for brevity, as the controls get added successfully but they are on the form itself rather than on the multipage control

Private Sub MultiPage1_Change()

Dim ws As Worksheet
Set ws = Worksheets("Params")
nCheckTopPosition = 20

If Me.MultiPage1.Value = 2 Then

' Service Areas

Dim cServiceA As Range
For Each cServiceA In ws.Range("Service_Areas")

'add check box, set properties
Dim chkBoxSA As Control
'use the count position to ensure unique name, chk1, chk2, etc
Set chkBoxSA = Me.Controls.Add("Forms.CheckBox.1", "chkCheck" & cServiceA.Value, True)
chkBoxSA.Left = 100
chkBoxSA.Top = nCheckTopPosition
'move the position for next checkbox
nCheckTopPosition = nCheckTopPosition + 20
'repeat for the controls label
Dim lblLabSA As Control
Set lblLabSA = Me.Controls.Add("Forms.Label.1", "lblCheck" & cServiceA.Value, True)
lblLabSA.Left = 115
lblLabSA.Top = nLabelTopPosition
nLabelTopPosition = nLabelTopPosition + 20
lblLabSA.Caption = cServiceA.Value
Next cServiceA

End If

End Sub

 

[Norie]

<S>Change Me.Controls.Add to Me.Multipage1.Controls.</S>
<S>
</S>That's wrong.

 

[mikerickson]

The line should be

Set chkBoxSA = Me.MultiPage1.Pages(0).Controls.Add("Forms.CheckBox.1", "chkCheck" & cServiceA.Value, True)

 

[Norie]

Try this:

Private Sub MultiPage1_Change()
Dim ctlPage As MSForms.Page
Dim chkBoxSA As Control
Dim lblLabSA As Control
    Set ctlPage = Me.MultiPage1.Pages(2)
    nCheckTopPosition = 10
    nLabelTopPosition = 10
    If Me.MultiPage1.Value = 2 Then
        ' Service Areas
        Dim cServiceA As Range
        For Each cServiceA In Range("Service_Areas")
            'add check box, set properties
            'use the count position to ensure unique name, chk1, chk2, etc
            Set chkBoxSA = ctlPage.Controls.Add("Forms.CheckBox.1", "chkCheck" & cServiceA.Value, True)
            chkBoxSA.Left = 100
            chkBoxSA.Top = nCheckTopPosition
            'chkBoxSA.Caption = cServiceA.Value
            'move the position for next checkbox
            nCheckTopPosition = nCheckTopPosition + 20
            'repeat for the controls label
            Set lblLabSA = ctlPage.Controls.Add("Forms.Label.1", "lblCheck" & cServiceA.Value, True)

            lblLabSA.Left = 115
            lblLabSA.Top = nLabelTopPosition
            nLabelTopPosition = nLabelTopPosition + 20
            lblLabSA.Caption = cServiceA.Value

        Next cServiceA
 
    End If
 
End Sub

This will put the controls on the 3rd page.

PS You could use the checkbox Caption property instead of labels.

 

[WatfordKev]

Thank you guys!!