VBA - translating code for ActiveX ListBox to forms ListBox

Question

I currently have code used for three ListBoxes in a worksheet (Box1, Box2, Box3), which are all filled with the same values. One box is single select, and the other two are multi select. I am having rendering issues (with the boxes growing in size when other people open the file) and thought it might be worth switching over to forms ListBoxes. I have not yet been able to get the code to translate.

Currently I have this function to populate the boxes:

Sub FillBox(MyBox As MSForms.ListBox, MultiType As Integer, DataArray As Variant)
    With MyBox
        .Clear
        .MultiSelect = MultiType
        For j = 1 To UBound(DataArray)
            .AddItem DataArray(j)
        Next j
    End With
End Sub

To glean a value from the single-select box, Box1, I use:

Sheets(2).Cells(1,1) = Box1.Value

To obtain values from Box2 and Box3 this type of code is employed:

For k = 0 To Box2.ListCount - 1
    If Box2.Selected(k) = True Then
        Sheets(2).Cells(k+1,2) = Box2.List(k)
    End If
Next k

I have been unable to get these same functions to work for a forms ListBox, including .Clear, .Selected(), .List(), etc. What is the alternative syntax for these types of operations? Or, if anything, is there a way to avoid bad rendering of ActiveX ListBoxes?

This is the rendering problem that has kicked off this whole thing in the first place: enter image description here

I dont understand nor can imagine what you mean by bad rendering. Can you show a screenshot or somehow better demonstrate it? — user2140173
– user2140173, Commented Apr 2, 2014 at 15:50
Sorry, just updated it now. I should say though I'm most interested in trying to get the code to work for the forms ListBox at this point. That's been the main frustration. — teepee
– teepee, Commented Apr 2, 2014 at 19:54

Andriy T. · Accepted Answer · 2014-11-06 09:22:14Z

I had the same problem and here is my solution:

I delete a list box, add it and fill it every time I open a book. So in this case a box will be in a range "B3:D17" every time

Private Sub Workbook_Open()
Set ws = Sheets("Hoja1")
    ws.OLEObjects("ListBox1").Delete

    Set Rng = ws.Range("B3:D17")

    Set mylist = ws.Range("G3:G7")

    ws.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
            DisplayAsIcon:=False, Left:=Rng.Left, Top:=Rng.Top, _
            Width:=Rng.Width, Height:=Rng.Height).Name = "ListBox1"

    ws.OLEObjects("ListBox1").ListFillRange = mylist.Address
End Sub

I found out another way, even better:

Resize your listbox on workbook open

Private Sub Workbook_Open()

    Set ws = Sheets("Hoja1")

    Set Rng = ws.Range("B3:D17")

    With ws.Shapes.Range(Array("ListBox1"))
        .Left = Rng.Left
        .Top = Rng.Top
        .Width = Rng.Width
        .Height = Rng.Height
    End With

End Sub

Collectives™ on Stack Overflow

VBA - translating code for ActiveX ListBox to forms ListBox

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related