'How to remove horizontal scroll bar in my listbox?

I am trying to get rid of the horizontal scroll bar in my listbox--which appears when a user clicks in certain cells and is then consequently "deleted" each time the user clicks out of that cell (so I can't change it manually, I must change it with code)--but the .ColumnWidths property does not seem to function.

It seems the ColumnWidths is default set at 74--this based on the fact that if I set my Width at 74 or greater there is no horizontal scroll bar.

If when clicking a cell, I go into design mode, open properties, I can manually set the ColumnWidths to 35. That is not a solution since my listbox is created and deleted depending on the user's active cell. Nonetheless this confirmed that it is something about how my code is written.

Option Explicit
 
Private WithEvents Lbx As MSForms.ListBox
Private oTarget As Range
Private ListBoxName As String
Private Const Cell_A1 As String = "B1:B20" 'change addr as required.


Private Sub Lbx_Change()
 
    Dim k As Long
    
    oTarget.ClearContents
    
    For k = 0 To Lbx.ListCount - 1
        If Lbx.Selected(k) Then
            If Len(oTarget) = 0 Then
                oTarget = Lbx.List(k)
            Else
                oTarget = _
                Trim(oTarget & vbNewLine & Lbx.List(k))
            End If
        End If
    Next

End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim oListBox As OLEObject

    On Error Resume Next
    Me.OLEObjects(1).Delete
    
    Range(Cell_A1).Interior.ColorIndex = 0
    
    If Target.Column = 2 And (Target.Row >= 1 And Target.Row <= 20) Then
    'UCase(Target.Address(0, 0)) = UCase(Cell_A1)
        Application.DisplayFormulaBar = False
        Set oListBox = _
        Me.OLEObjects.Add(ClassType:="Forms.ListBox.1")
        With oListBox
             Names.Add "ListBoxName", .Name
            .Left = Target.Offset(0,1).Left
            .Top = Target.Offset(0, 0).Top
            .ColumnCount = 1
            .ColumnWidths = "35"
            .Width = 54
            .Height = Me.StandardHeight * 16
            .Object.ListStyle = fmListStylePlain
            .ListFillRange = "A1:A20"
            .Placement = xlFreeFloating
            .Object.MultiSelect = fmMultiSelectMulti
            .Object.SpecialEffect = fmSpecialEffectFlat
            .Object.BorderStyle = fmBorderStyleSingle
            With Application
                .OnTime Now + _
                TimeSerial(0, 0, 0.01), Me.CodeName & ".Hooklistbox"
                .CommandBars.FindControl(ID:=1605).Execute
            End With
        End With
    Else
        Application.DisplayFormulaBar = True
        Names("ListBoxName").Delete
        Range(Cell_A1).Interior.ColorIndex = 0
    End If
 
End Sub
 

Private Sub Hooklistbox()
 
    Application.CommandBars.FindControl(ID:=1605).Reset
    Set oTarget = ActiveCell
    ActiveCell.Interior.Color = vbGreen
    'display the listbox and hook it.
    With Me.OLEObjects(Evaluate("ListBoxName"))
        .Visible = True
        Set Lbx = .Object
    End With
    
End Sub

Solution 1:^[1]

Type

.Object.

Before .ColumnCount and .ColumnWidths And get rid of the on error resume next, which brought you to this "hidden" error in the first place Use a on error goto 0 afterwards when it's not needed anymore

++ instead of:

On Error Resume Next
    Me.OLEObjects(1).Delete

you could use:

If Me.OLEObjects.Count > 0 Then Me.OLEObjects(1).Delete

and delete this line (because Names will be overwritten, so no need to delete:

 Names("ListBoxName").Delete

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1

'How to remove horizontal scroll bar in my listbox?

Solution 1:[1]

Sources

Related Questions

Solution 1:^[1]