Excel VBA 表单控件 - 重置列表框滚动条

Question

我编写了下面的代码来从同一张表上的六个列表框(多选)中提取数据，将选择传递给模块进行计算，然后清除列表框选择。尽管非常令人沮丧，但似乎可以忽略不计的唯一问题是让每个列表框的列表框“滚动条”将其位置重置为列表顶部。

我试过 .TopIndex 但因为我使用的是表单控件而不是 ActiveX 控件，所以它返回不支持的“对象”。

有谁知道如何将列表框滚动条位置重置到表单控件列表框的顶部？

Sub Listboxproperties_click()

    'store selected items from listbox into an array
    Dim listarray()
    Dim J As Integer
    Dim R As Integer
    Dim i As Integer

    'Add selected items into the array
    ReDim listarray(1 To 50, 1 To 6)
    'Counter
    J = 0
    For R = 1 To 6
        Set lb = ActiveSheet.ListBoxes("ListBox" & R)
        For i = 1 To lb.ListCount
            If lb.Selected(i) = True Then
                'add 1 to the counter
                J = J + 1
                'Store selection in an array
                listarray(J, R) = lb.list(i)
            End If
        Next i
        J = 0
    Next R

    'Check if msgbox has a selection if not exit sub
    For R = 1 To 6
        'if there is nothing in the first item of the listarray then the user has not chosen an option
        If listarray(1, R) = "" Then
            MsgBox "You have not selected a option, please select and retry"
            Exit Sub
        End If
    Next R

    'input box for the name of the Trend

    Linename = InputBox("Please enter a name for the call type you are calculating i.e. Adviser Calls, Withdrawal   Status etc", "Call Trend")

    If Linename = "" Then
        MsgBox "No name selected, please retry and enter a name for your call flow"
        Exit Sub
    End If

    Call UniqueCount(listarray, Linename)

    'clear selections from listbox
    For R = 1 To 6
        Set lb = ActiveSheet.ListBoxes("ListBox" & R)
        For i = 1 To lb.ListCount - 1
            If lb.Selected(i) = True Then
                lb.Selected = False
            End If
        Next i
        lb.TopIndex
    Next R

End Sub    

Answer 1

我发现它的唯一方法是清理并重新插入值......

Dim xx(1 To 10000) As String
Set o = ActiveSheet.Shapes("List Box 1")
e = 1
For e = 1 To o.ControlFormat.ListCount
    xx(e) = o.ControlFormat.List(e)
Next
o.ControlFormat.RemoveAllItems
For i = 1 To e
    o.ControlFormat.AddItem xx(i)
Next
o.ControlFormat.ListIndex = 1

因为 列表索引 选择第一个元素，但不是事件的，你不能使用键盘在里面移动......