How to create MSForms ListBox in Worksheet using VBA?

Question

I'm trying to create a list box of the type MSForms.ListBox programmatically using VBA.

I can't do it with Set ListBox = New MSForms.ListBox because it throws the compile error: Invalid use of the New keyword.

In the code below when I create an OLEObject in Macro1 it creates VBAProject.Sheet1.ListBox1 (or other number) which I can then (after execution has ended) assign in Macro2 to a variable of type MSForms.ListBox but it only works if I run one macro at a time.

With a MSForms.ListBox I can then change properties like ListBox.ColumnHeads = True (even though I don't know how to change the head value other than addressing the list values to a range with ListBox.ListFillRange = RangeAddress).

If I try to execute the code step by step I get the message Can't enter break mode at this time.

I got OLEObject from recording a macro and inserting a List Box ActiveX Control.

' Microsoft Excel 2013 built-in references:
' Excel - Microsoft Excel 15.0 Object Library
' VBA - Visual Basic For Applications

' VBA project library:
' VBAProject

' Aditional references:
' MSForms - Microsoft Forms 2.0 Object Library

Private Sub Macro1()

  Dim Worksheet As Excel.Worksheet
  Dim ListBox As Excel.ListBox
  Dim Shape As Excel.Shape
  Dim OLEObject As Excel.OLEObject

  Set Worksheet = VBAProject.Sheet1
  Worksheet.Range("A1").Value = "Header"
  Worksheet.Range("A2").Value = "Value 1"
  Worksheet.Range("A3").Value = "Value 2"
  Worksheet.Range("A4").Value = "Value 3"

  For Each Shape In Worksheet.Shapes
    Shape.Delete
  Next Shape

  Set ListBox = Worksheet.ListBoxes.Add(60, 10, 100, 100)
  ListBox.List = Array("Header", "Value 1", "Value 2", "Value 3")
  ListBox.ListFillRange = "A1:A4"

  Set OLEObject = Worksheet.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, Left:=170, Top:=10, Width:=100, Height:=100)
  OLEObject.ListFillRange = "A1:A4"

  Set Shape = Worksheet.Shapes.AddOLEObject(ClassType:="Forms.ListBox.1", Link:=False, Left:=280, Top:=10, Width:=100, Height:=100)

End Sub

Private Sub Macro2()

  Dim Worksheet As Excel.Worksheet
  Dim ListBox As MSForms.ListBox

  Set Worksheet = Excel.Application.ActiveSheet

  Set ListBox = VBAProject.Sheet1.ListBox1
  ListBox.ListFillRange = ""
  ListBox.List = Array("Header", "Value 1", "Value 2", "Value 3")
  ListBox.ColumnHeads = True
  ListBox.ListFillRange = "A1:A4"
  ListBox.BorderStyle = MSForms.fmBorderStyle.fmBorderStyleSingle

End Sub

Edit:

Simple working example using the solution given in the accepted answer:

Private Function CreateListBox( _
  Optional ByVal Worksheet As Excel.Worksheet = Nothing, _
  Optional ByVal Width As Long = 100, _
  Optional ByVal Height As Long = 100, _
  Optional ByVal Left As Long = 0, _
  Optional ByVal Top As Long = 0 _
  ) As MSForms.ListBox

  Const ClassType As String = "Forms.ListBox.1"

  If Worksheet Is Nothing Then
    Set Worksheet = Excel.Application.ActiveSheet
  End If

  Set CreateListBox = Worksheet.OLEObjects.Add( _
    ClassType, _
    Left:=Left, _
    Top:=Top, _
    Width:=Width, _
    Height:=Height).Object

End Function

Private Sub Test()

  Dim ListBox As MSForms.ListBox

  Set ListBox = CreateListBox
  Stop ' Able to stop/suspend code execution here but not inside the function when creating the OLEObject

End Sub

[This](https://wellsr.com/vba/2016/excel/complete-guide-to-excel-vba-form-control-listboxes/#add) might help — Zac, Jan 16 '20 at 13:11
@Zac I have seen that but it creates an `Excel.ListBox` which has different properties than the ones I want to change. — user7393973, Jan 16 '20 at 13:14
In short you want to run both the above macros in one go? i.e create an activex listbox and then set it's poperties? — Siddharth Rout, Jan 16 '20 at 13:18
You shouldn't create a variable named `Shape`, as that is a defined object type. — PeterT, Jan 16 '20 at 13:20
@PeterT I am aware that it goes against naming conventions / best practices as it overwrites `Shape` even thought I access the object type with `Excel.Shape`. — user7393973, Jan 16 '20 at 13:30

Siddharth Rout · Accepted Answer · 2020-01-16T13:46:13.930

When you press . within the With lb...End With code block, you will see that the intellisense does not reveal certain properties for example .ColumnHeads, .BorderStyle or .List. You can access those properties by prefixing it with .Object

Is this what you are trying?

Sub Sample()
    Dim lb As OLEObject
    Dim ws As Worksheet

    Set ws = Sheet1

    For Each lb In ws.OLEObjects
        lb.Delete
    Next lb

    Set lb = ws.OLEObjects.Add(ClassType:="Forms.ListBox.1", _
                               Top:=60, _
                               Left:=10, _
                               Height:=100, _
                               Width:=100)

    With lb
        .ListFillRange = "'" & ws.Name & "'!A1:A16" '<~~ Change range here
        '.Object.List = Array("Header", "Value 1", "Value 2", "Value 3")
        .Object.ColumnHeads = True
        .Object.BorderStyle = MSForms.fmBorderStyle.fmBorderStyleSingle
    End With
End Sub

In Action

That's it! I did tried something similar before but didn't got it working. That way I can assign `lb.Object` to a variable of type `MSForms.ListBox` as well. Thank you :) — user7393973, Jan 16 '20 at 13:49

How to create MSForms ListBox in Worksheet using VBA?

1 Answers1