VBA Code to generate controls on a userform

bbrother

New Member
Joined
Nov 23, 2015
Messages
41
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

bbrother

New Member
Joined
Nov 23, 2015
Messages
41
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.

Code:
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

Well-known Member
Joined
Nov 3, 2011
Messages
1,367
Here is some code that may help. It puts one ScrollBar on the form. Note: the WithEvents declaration enables events on the scroll bar.

Code:
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

New Member
Joined
Nov 23, 2015
Messages
41
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,778
Members
425,494
Latest member
Ragamacam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top