title: How to create MSForms ListBox in Worksheet using VBA?
body: <p>I'm trying to create a list box of the type <code>MSForms.ListBox</code> programmatically using VBA.</p>
  
  <p>I can't do it with <code>Set ListBox = New MSForms.ListBox</code> because it throws the compile error: <a href="https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/invalid-use-of-new-keyword" rel="nofollow noreferrer"><code>Invalid use of the New keyword</code></a>.</p>
  
  <p>In the code below when I create an <a href="https://docs.microsoft.com/en-us/office/vba/api/excel.oleobject" rel="nofollow noreferrer"><code>OLEObject</code></a> in <code>Macro1</code> it creates <code>VBAProject.Sheet1.ListBox1</code> (or other number) which I can then (after execution has ended) assign in <code>Macro2</code> to a variable of type <code>MSForms.ListBox</code> but it only works if I run one macro at a time.</p>
  
  <p>With a <code>MSForms.ListBox</code> I can then change properties like <code>ListBox.ColumnHeads = True</code> (even though I don't know how to change the head value other than addressing the list values to a range with <code>ListBox.ListFillRange = RangeAddress</code>).</p>
  
  <p>If I try to execute the code step by step I get the message <a href="https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/can-t-enter-break-mode-at-this-time" rel="nofollow noreferrer"><code>Can't enter break mode at this time</code></a>.</p>
  
  <p>I got <code>OLEObject</code> from recording a macro and <a href="https://www.excel-easy.com/vba/examples/list-box.html" rel="nofollow noreferrer">inserting a List Box ActiveX Control</a>.</p>
  
  <pre><code>' 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
  </code></pre>
  
  <hr>
  
  <p><strong>Edit:</strong></p>
  
  <p>Simple working example using the solution given in the accepted answer:</p>
  
  <pre><code>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
  </code></pre>