VBA Code to generate controls on a userform

[bbrother]

Dear Mr. Excel,

I've searched quite a bit but can't find the appropriate search term to zero in on the answer, or it just can't be done. (Never!)

I am designing a form that needs a variable number of ScrollBars. I need to generate scrollbars from within a loop, so that there is 1 scrollbar per list item.

I have no idea where to even start, but I have a sneaking suspicion it's something like a class, or an object, or some other nebulous sounding construct.

Any ideas or links to resources, or hints on search terms would be greatly appreciated!

Thanks in advance,
Bill

 

[bbrother]

I think I have it. I am new and self taught to VBA, so try not to laugh too hard...

Rolemaker is a userform,
UseFrame is a frame on a multipage
UserNames is a multicolumn listbox
UsePage is a privatesub so I can build what I want to display on this page

Since this page continually changes, I needed to clear out any leftover scrollbars before generating new ones.

Private Sub UsePage()    'this sub drives the token pool sizing page
    ' it is called after the other pages are up to date, but before changes are applied to the sheet
    ' get the most current data from the forms
    Dim name, userlabel, dbspace, lname As String
    Dim i As Integer
    Dim bar(20), cCont As Control
    
    dbspace = Chr$(13) & Chr$(10) & Chr$(13) & Chr$(10)


    For Each cCont In RoleMaker.UseFrame.Controls
        If TypeName(cCont) = "ScrollBar" Then
            lname = cCont.name
            RoleMaker.UseFrame.Controls.Remove lname
            End If
        Next cCont
    
    For i = 0 To RoleMaker.UserNames.ListCount - 1
        userlabel = userlabel & dbspace & RoleMaker.UserNames.Column(1, i)
        Set bar(i) = RoleMaker.UseFrame.Controls.Add("Forms.Scrollbar.1", name:="Bar" & i, Visible:=True)
        With bar(i)
            .Left = 100
            .Top = 12 * 2 * i + 30
            .Width = 100
            .Height = 12
            .Visible = True
            If i <> 0 Then Enabled = False
            End With
    Next
    
    PersonaUseList.Caption = userlabel
End Sub

 

[tlowry]

Here is some code that may help. It puts one ScrollBar on the form. Note: the WithEvents declaration enables events on the scroll bar.

Public WithEvents sb As MSForms.ScrollBar
Private Sub UserForm_Initialize()
     Set sb = Me.Controls.Add("Forms.Scrollbar.1", "SB001")
End Sub
Private Sub sb_Change()
     MsgBox sb.Value
End Sub

 

[bbrother]

Thanks tlowry, your code is far more elegant than what I have.

I figured out the add, but I couldn't use the initialize since there are many pages to this form, and this list of scrollbars needs to dynamically changed as other data in the form (wizard) changes as well - which is why I run through and delete any in the frame before adding the new ones.

You've hit on my latest bottleneck, and that is now that I've draw the bars, I have to trap any changes that occur - but I don't know how many bars will exist at any given time. I think I've created a 'Controls Array' so now I am reading up on how to deal with that...

Thank you for your time!

Best regards,
Bill