vba Excel 列表框 listindex 不突出显示

Excel Listbox listindex does not highlight

提问by user2296377

Could someone with a better level of excel capability please assist me with the below code? I have set this up in the Worksheet_Activate event. I have code to set an ActiveX listbox to a default value, as shown below. For whatever reason, the listbox is not showing the default value as a highlighted item. All the other logic seems to work fine but it's driving me crazy that the below code won't highlight the stupid first item in my first listbox. What am I doing wrong?

有更高水平的 excel 能力的人可以帮助我使用以下代码吗？我已经在 Worksheet_Activate 事件中进行了设置。我有将 ActiveX 列表框设置为默认值的代码，如下所示。无论出于何种原因，列表框都没有将默认值显示为突出显示的项目。所有其他逻辑似乎都可以正常工作，但下面的代码不会突出显示我的第一个列表框中愚蠢的第一项，这让我发疯。我究竟做错了什么？

With CTOverview.ListBox1
    .IntegralHeight = True
    .Height = 114.75
    .Width = 125.25
    .IntegralHeight = False
    .ListIndex = 0
    .Selected(0) = True
    .Value = "Entire Division"
End With

CTData.Range("Overview_RegionSelected").Value = CTOverview.ListBox1.Value

With CTOverview.ListBox2
    .IntegralHeight = True
    .Height = 114.75
    .Width = 150
    .IntegralHeight = False
    .ListIndex = -1
End With

Thanks for any help.

谢谢你的帮助。

采纳答案by Siddharth Rout

I know. I have seen this weird behavior in the past with absolutely no explanation. Sometime it works and sometimes it doesn't. Try this. This will work.

我知道。我过去曾见过这种奇怪的行为，完全没有任何解释。有时它有效，有时它不起作用。尝试这个。这将起作用。

With CTOverview.ListBox1
    If .ListCount > 1 Then .Selected(1) = True
    .IntegralHeight = True
    .Height = 114.75
    .Width = 125.25
    .IntegralHeight = False
    .ListIndex = 0
    .Selected(0) = True
    .Value = "Entire Division"
End With

FOLLOWUP

跟进

Here is a better code than the code that I gave you earlier. The above code was restricted to the fact that we needed to have morethan 1 Listcount. The below will work for 1 Listcountas well.

这是一个比我之前给你的代码更好的代码。上面的代码被限制的事实，我们需要有更多比1 Listcount。以下内容也适用于 1 Listcount。

Dim rng As Range, aCell As Range

With CTOverview.ListBox1
    .IntegralHeight = True
    .Height = 114.75
    .Width = 125.25
    .IntegralHeight = False
    .ListIndex = 0
    .Selected(0) = True
    .Value = "Entire Division"

    Set rng = Range(.ListFillRange)
    For Each aCell In rng
        aCell.Formula = aCell.Formula
    Next
End With

回答by Joe Clotpole

Setting .Selected to False and immediately setting it to True worked for me:

将 .Selected 设置为 False 并立即将其设置为 True 对我有用：

With Me.Charts_LB
    .ColumnHeads = False
    .RowSource = rng.Address
    .Height = 480
    .Selected(Charts_ListIndex) = False
    .Selected(Charts_ListIndex) = True
End With

回答by MeenakshiSundharam

Simplify your code. Either use the .Selectedproperty or the .ListIndexproperty or the .Valueproperty to have an item in the list selected.

简化您的代码。使用.Selected属性或.ListIndex属性或.Value属性来选择列表中的项目。

In your first listbox (ListBox1), you have used all the three properties to have your default selection in effect. Probably you are having inconsistent values in your code like the first item of the list not being "Entire Division". If your first item is "Entire Division" only, then have any one of the properties in place to effect your desired selection.

在您的第一个列表框 (ListBox1) 中，您已使用所有三个属性来使您的默认选择生效。可能您的代码中存在不一致的值，例如列表的第一项不是“整个部门”。如果您的第一个项目只是“整个部门”，那么请准备好任何一个属性来实现您想要的选择。

In the second listbox (ListBox2), when your code has .ListIndex= -1, it means that you want to deselect everything. So change the desired list index to 0 to have the first item selected.

在第二个列表框 (ListBox2) 中，当您的代码具有.ListIndex= -1 时，表示您要取消选择所有内容。因此，将所需的列表索引更改为 0 以选择第一个项目。

Given that you want the first options of the list boxes selected by default, have the following as a base.

假设您希望默认选择列表框的第一个选项，请以以下内容为基础。

With CTOverview.ListBox1
    .ListIndex = 0
End With

With CTOverview.ListBox2
    .ListIndex = 0
End With

Let me know your thoughts.

让我知道你的想法。

回答by DhruvJoshi

So I had the same problem and I referred the Office documentation and realized that everything depended on the premise that the listbox should be in focus to have its value attribute to return value.

所以我遇到了同样的问题，我参考了 Office 文档并意识到一切都取决于列表框应该处于焦点以使其 value 属性返回值的前提。

I propose that you simply add a line at the end to bring focus.

我建议您只需在末尾添加一行即可聚焦。

With CTOverview.ListBox1
    .IntegralHeight = True
    .Height = 114.75
    .Width = 125.25
    .IntegralHeight = False
    .ListIndex = 0
    .Selected(0) = True
    .Value = "Entire Division"
    .SetFocus
End With

回答by pstraton

I have seen a similar problem with ListBox controls in UserForms (not ActiveX controls embedded in a worksheet), but since I haven't found any other postings that show up on the first couple of Google search pages, I'm attaching my solution to this one, since it is likely that people hunting for UserForm solutions will hit on this post, plus it may also be helpful to some who are using straight ActiveX ListBox controls.

我在 UserForms 中看到了 ListBox 控件的类似问题（不是嵌入在工作表中的 ActiveX 控件），但由于我没有找到任何其他出现在前几个 Google 搜索页面上的帖子，我将我的解决方案附加到这个，因为寻找 UserForm 解决方案的人很可能会看到这篇文章，而且它也可能对一些使用直接 ActiveX ListBox 控件的人有所帮助。

In UserForms (at least) setting .ListIndex = n and .Selected(n) = True doesn't seem to help at all. The only workaround that I've found is this, where "n" is the list item to be selected/highlighted:

在用户窗体中（至少）设置 .ListIndex = n 和 .Selected(n) = True 似乎根本没有帮助。我发现的唯一解决方法是，其中“n”是要选择/突出显示的列表项：

Dim SaveVal As String
With MyUserForm.SomeListBox
    SaveVal = .List(n)
    .Value = ""
    .Value = SaveVal
    .SetFocus   'Not required, but allows up/down arrow keys to function normally.
End With

Note that just re-assigning the selected list item to its current value doesn't work because the underlying code apparently checks for that case and treats it as a no-op.

请注意，仅将所选列表项重新分配为其当前值是行不通的，因为底层代码显然会检查这种情况并将其视为无操作。