Excel ActiveX组合框项目选择宏

Someone asked how they could show the items in a worksheet Combobox list, one after the other. It was an interesting challenge, so here is my Activex Combobox item select macro.

有人问他们如何将这些项目显示在工作表的“组合框”列表中，一个接一个。 这是一个有趣的挑战，因此这是我的Activex组合框项目选择宏。

组合框宏 (Combobox Macros)

Several of my Excel sample files use ActiveX comboboxes with data validation. Click on a cell that has a drop down list, and a combobox appears, showing the items from the selected cell's drop down.

我的一些Excel示例文件使用ActiveX组合框进行数据验证。 单击具有下拉列表的单元格，然后出现一个组合框，其中显示了所选单元格下拉列表中的项目。

For example, click here to see the combobox example that works with named ranges. It even shows the list for a simple dependent list, based on the INDIRECT function.

例如，单击此处以查看适用于命名范围的组合框示例 。 它甚至基于INDIRECT函数显示一个简单的依赖列表的列表。

This short video shows how the combobox works with the drop down lists.

这个简短的视频展示了组合框如何与下拉列表一起使用。

演示地址

组合框项目列表 (Combobox Items List)

For a worksheet ActiveX combobox, you can set the ListFillRange property to an address or a range name, to show a specific list of items.

对于工作表ActiveX组合框，可以将ListFillRange属性设置为地址或范围名称，以显示特定的项目列表。

In this screen shot, the combobox will show the months from the named range, ListA.

在此屏幕快照中，组合框将显示命名范围ListA中的月份。

列出属性问题 (List Property Problem)

In all the years that I've worked with ActiveX comboboxes, I'd never tried to loop through the list of items. And in this case, the question was about worksheet comboboxes, which don't work the same as UserForm comboboxes, so sample code for those wouldn't work.

在使用ActiveX组合框的所有年份中，我从未尝试遍历项目列表。 在这种情况下，问题在于工作表组合框，它们与UserForm组合框的工作方式不同，因此这些示例代码将无法工作。

After some experimenting and hunting, I found that you can use the Object property for the combobox, and that was the secret to looping through the list of items.

经过一些试验和搜寻，我发现您可以将Object属性用于combobox ，这是遍历项目列表的秘密。

If you try this line of code for the List property, Excel shows an error:

如果您为List属性尝试以下代码行，Excel将显示错误：

Debug.Print cboTest.List(1)

调试。打印cboTest.List(1)

But, if you include the Object property, the line works:

但是，如果包含Object属性，则该行有效：

Debug.Print cboTest.Object.List(1)

调试打印cboTest.Object.List(1)

循环通过组合框项目 (Loop Through Combobox Items)

With the Object property, I was able to create code that finds the current value in the combobox, and moves to the next value.

使用Object属性，我能够创建代码，以在组合框中找到当前值，然后移动到下一个值。

The code is stored in a regular code module, and there is a combobox named "TestCombo", on the worksheet named "ComboBox".

该代码存储在常规代码模块中，并且在名为“ ComboBox”的工作表上有一个名为“ TestCombo”的组合框。

NOTE: There's no error checking in this macro, so you can add that if you use this code in your files.

注意：此宏中没有错误检查，因此，如果在文件中使用此代码，则可以添加该代码。

Sub ChangeComboValue()
Dim cboTest As OLEObject
Dim wsCB As Worksheet
Dim lListCount As Long
Dim lListGo As Long

Set wsCB = Sheets("ComboBox")
Set cboTest = wsCB.OLEObjects("TestCombo")

With cboTest.Object
  lListCount = .ListCount
  If .ListIndex = .ListCount - 1 Then
    lListGo = 0
  Else
    lListGo = .ListIndex + 1
  End If
  .Value = .List(lListGo)
End With

End Sub

获取样本工作簿 (Get the Sample Workbook)

In the sample file, there is a button on the worksheet, above the ActiveX combobox. Click that button, to select the next item in the list.

在示例文件中，工作表上ActiveX组合框上方有一个按钮。 单击该按钮，以选择列表中的下一项。

NOTE: If the combobox is empty, or if you get to the end of the list, the first item is shown.

注意：如果组合框为空，或者您到达列表的末尾，则显示第一项。

To get the sample file, go to the Excel Sample Files page, and in the Data Validation section, look for DV0073 -Data Validation Combobox - Select Next Item.

要获取示例文件，请转到“ Excel示例文件”页面 ，然后在“数据验证”部分中查找DV0073-“数据验证组合框”-选择“下一项” 。

The zipped workbook is in xlsm format, and contains macros. When you open the file, be sure to enable macros, if you want to test the combobox and button.

压缩的工作簿为xlsm格式，包含宏。 打开文件时，如果要测试组合框和按钮，请确保启用宏。

翻译自: https://contexturesblog.com/archives/2018/08/23/excel-activex-combobox-item-select-macro/