如何使用 VBA 在数据验证列表中选择下一项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16889492/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to select next item in Data Validation list using VBA
提问by user2446371
I have a Data Validation list with dates in it. When you change the date it effects what data is shown in the rest of the worksheet.
我有一个包含日期的数据验证列表。当您更改日期时，它会影响工作表其余部分中显示的数据。
I would like to to create 2 command buttons,
我想创建 2 个命令按钮，
- "Next" - when clicked will move to the next date in the list, when it reaches the end of the list it goes back to the beginning of the list
- "Previous" - when clicked will move to the previous date in the list, when it reaches the beginning of the list it will go to the end of the list
- “下一个” - 单击时将移动到列表中的下一个日期，当它到达列表末尾时将返回到列表的开头
- “上一个” - 单击时将移动到列表中的上一个日期，当它到达列表的开头时将转到列表的末尾
Is this possible?
这可能吗？
I did look at List Box and Combo Box but got highly confused with the coding!!! Any help would be great!
我确实看过列表框和组合框，但对编码非常困惑！！！任何帮助都会很棒！
回答by MikeD
You have to distinguish 3 cases:
你必须区分3种情况：
- data validation with in-line list elements (e.g. Source = "1;2;3;4;5")
- data validation with list elements in a range
- List/Combo box
- 使用内嵌列表元素进行数据验证（例如 Source = "1;2;3;4;5"）
- 使用范围内的列表元素进行数据验证
- 列表/组合框
Case 1 is maybe the most difficult, because you can access the list elements only in a string and have to split them up into an array, get the index of the current selection and move the index with two buttones in order to get the wanted result
情况 1 可能是最困难的，因为您只能在字符串中访问列表元素，并且必须将它们拆分成一个数组，获取当前选择的索引并使用两个按钮移动索引以获得想要的结果
Case 2 is a bit simpler, but again you need to somehow keep track of the current position within the range defining your dates
情况 2 稍微简单一点，但您再次需要以某种方式跟踪定义日期范围内的当前位置
Case 3 is maybe the most easiest to implement ... but still requires a certain coding effort, like
情况 3 可能是最容易实现的……但仍然需要一定的编码工作，例如
- load list of dates into the list/combo box before first display (OnLoad or OnActivate
- create code for the UP and DOWN buttons to increment/decrement the list box index, with automatic wrap-around
- 在首次显示之前将日期列表加载到列表/组合框中（OnLoad 或 OnActivate
- 为 UP 和 DOWN 按钮创建代码以增加/减少列表框索引，自动环绕
I suggest a 4th case to you ... use an ActiveX Spin Button ... this provides up and down functions in one element:
我建议您使用第 4 种情况……使用 ActiveX 旋转按钮……这在一个元素中提供了向上和向下功能：
- create your list of dates in a vertical named range DateList
- reserve one more cell for the index of the Spin Button and name it DateIndex
- using Developer ribbon, insert an ActiveX Spin Button (default name is SpinButton1)
- set the LinkedCell property in SpinButton1(be sure to be in Developer / Design mode; right click the Spin button and select "properties") to DateIndex
- create the following code (still in design mode, right click the SpinButton and select "view code")
- 在垂直命名范围DateList 中创建日期列表
- 为 Spin Button 的索引再保留一个单元格并将其命名为DateIndex
- 使用开发人员功能区，插入 ActiveX 旋转按钮（默认名称为SpinButton1）
- 将SpinButton1 中的 LinkedCell 属性（确保处于开发人员/设计模式；右键单击 Spin 按钮并选择“属性”）为DateIndex
- 创建以下代码（仍处于设计模式，右键单击 SpinButton 并选择“查看代码”）
code
代码
Private Sub SpinButton1_SpinDown()
If SpinButton1 = 0 Then
SpinButton1 = Range("DateList").Rows.Count
End If
End Sub
Private Sub SpinButton1_SpinUp()
If SpinButton1 = Range("DateList").Rows.Count + 1 Then
SpinButton1 = 1
End If
End Sub
- At the cell where you want to display the selected date, enter formula
=INDEX(DateList,DateIndex) - since you are working with named ranges, DateList and DateIndex may be in a different sheet (even a hidden one) than the user sheet.
- if you want to create a copy of the currently chosen date at the cell where the user has currently put the cursor, add the following statement to the end of the SpinDown/Up Sub's (after
End If:Selection = Range("DateList").Cells(SpinButton1, 1)
- 在要显示所选日期的单元格中，输入公式
=INDEX(DateList,DateIndex) - 由于您正在使用命名范围，因此 DateList 和 DateIndex 可能与用户工作表位于不同的工作表（甚至是隐藏的工作表）中。
- 如果要在用户当前放置光标的单元格中创建当前所选日期的副本，请将以下语句添加到 SpinDown/Up Sub 的末尾（在 之后
End If：Selection = Range("DateList").Cells(SpinButton1, 1)
