VBA-关闭/打开/撤回组合框列表？ [英] VBA - close/unwrap/retract a combobox list?

问题描述

我正在处理一个动态填充的Excel组合框（嵌入在工作表中）

I'm working on a dynamically filled Excel combobox (embeded in worksheet)

但是有时候当我在列表已经下拉/展开时更新列表时，

But sometimes when I update the list inside while it's already "dropdown/unwrap", the display gets crazy.

在我填写并检查列表时，我使用了这些：

As I fill and check the list, I use these :

1. 调整可见行数

1. to adjust the number of visible lines

If .ListCount > 14 Then
    .ListRows = 15
Else
    .ListRows = .ListCount + 1
End If

显示/取消折叠列表（anyCB是我的子对象中的Object参数）

to display/unwrap the list (anyCB is an Object parameter in my Sub)

anyCB.DropDown

但有时，仍然有15条可见线，但是大（15）条中有一个litlle滑块，可以在单个行中滚动浏览所有行...：/

But sometimes, there is still 15 visible lines but a litlle slider inside the big (15) one, to scroll through all the lines in a single one... :/

所以我想知道在更改可见行数之前，是否有任何方法可以关闭/解开/收回列表。您可以建议使用其他解决方法（失去焦点，...）

So I'm wondering if there is any way to close/unwrap/retract the list before changing the number of visible lines. On any other workaround (lost focus, ...) that you could suggest ;)

以下是这两个奇怪的屏幕截图我遇到的情况：

Here are screenshots of both strange cases that I've got :

在常规组合框上应复制的内容：

What should reproduce on a regular combobox :

    For i = 0 To 100
        anyCB.AddItem (i)
    Next i

    With anyCB
        If .ListCount > 14 Then
            .ListRows = 15
        Else
            .ListRows = .ListCount + 1
        End If
    End With
    anyCB.DropDown

    If .ListCount > 0 Then
        For i = .ListCount - 1 To 0 Step -1
            .RemoveItem i
        Next i
    End If
    For i = 0 To 100
        anyCB.AddItem (i)
    Next i
    anyCB.DropDown

推荐答案

这是一个错误。您可以通过以下两种方式来处理此问题

This is a bug. There are two ways you can take care of this

方法1

商店数组中的值，然后将该数组绑定到组合框

Store the values in an array and then bind the array to the combobox

Option Explicit

Sub Sample()
    Dim i As Long
    Dim MyAr(100)

    anyCB.Clear

    With anyCB
        '~~> This is required because if you run this
        '~~> procedure for the 2nd time with the dropdown
        '~~> visible then you will face the problem again
        .Activate

        For i = 0 To 100
            MyAr(i) = i
        Next i

        .List = MyAr
        DoEvents
        .DropDown
    End With
End Sub 

WAY 2

1. 致电 .DropDown


2. 选择一个单元格（ Sheeesh !!! ）


3. 再次调用 .DropDown
   

1. Call .DropDown
2. Select a Cell (Sheeesh!!!)
3. Call .DropDown again

例如

Option Explicit

Sub Sample()
    Dim i As Long

    anyCB.Clear

    With anyCB
        For i = 0 To 100
            .AddItem (i)
        Next i

        If .ListCount > 14 Then
            .ListRows = 15
        Else
            .ListRows = .ListCount + 1
        End If

        .DropDown

        If .ListCount > 0 Then
            For i = .ListCount - 1 To 0 Step -1
                .RemoveItem i
            Next i
        End If

        For i = 0 To 100
            .AddItem (i)
        Next i

        .Activate
        .DropDown
        [A1].Activate
        .DropDown
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Not Application.Intersect(Target, Me.Range("A1")) Is Nothing Then
        Application.EnableEvents = False
        '~~> Change the selection to another cell, so that it'll work multiple times
        Me.Range("A2").Activate
        Application.EnableEvents = True
        DoEvents
        anyCB.Activate
        Exit Sub
    End If
End Sub

这篇关于VBA-关闭/打开/撤回组合框列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！