Excel ComboBox-仅自动调整大小下拉列表 [英] Excel ComboBox - Autosize Dropdown Only

问题描述

是否可以在用户窗体中仅使ComboBox的下拉菜单自动适应文本大小，而无需更改ComboBox的实际大小?

Is it possible to have just the drop down menu of a ComboBox in a UserForm autofit to the text size, without changing the actual size of the ComboBox?

我已经找到了一些有关如何根据其中的值自动拟合实际ComboBox的答案，但这使大小超出了我的实际需求(

I've found some answers on how to autofit the actual ComboBox based on the values within, but that makes the size bigger than I actually want (link here).

以下图像在某种程度上代表了我要完成的工作:

The following image somewhat represents what I'm trying to accomplish:

有人知道这是否有可能吗?

Does anyone know if this is even possible?

推荐答案

有些列似乎有点宽，但总的来说，我认为代码在配置下拉列表方面做得很好.

Some of the columns seem a little wide, but over all I think the code does a pretty good job of configuring the drop down.

Private Sub ConfigureComboBox()
    Dim arrData, arrWidths
    Dim x As Long, y As Long, ListWidth As Double
    arrData = ComboBox1.List
    ReDim arrWidths(UBound(arrData, 2))

    For x = 0 To UBound(arrData, 1)
        For y = 0 To UBound(arrData, 2)

            If Len(arrData(x, y)) > arrWidths(y) Then arrWidths(y) = Len(arrData(x, y))

        Next
    Next

    For y = 0 To UBound(arrWidths)

        arrWidths(y) = arrWidths(y) * ComboBox1.Font.Size
        ListWidth = ListWidth + arrWidths(y)
    Next

    With ComboBox1
        .ColumnCount = UBound(arrWidths) + 1
        .ColumnWidths = Join(arrWidths, ";")
        .ListWidth = ListWidth
    End With

End Sub

来自 Excel示例数据

这篇关于Excel ComboBox-仅自动调整大小下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！