vba 在 Excel 下拉选项卡后的焦点上，自动显示列表以供选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6525462/
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
On focus after tabbing of Excel drop down, automatically show list for selection
提问by Anil
I have some data validation drop down lists in excel, I can tab through all the lists but I have to press alt + down arrow to show the list, Is their a way it can be automatically shown as soon as I focus on the tab.
So on focus of the drop down list, I would like the list to appear So that I can select it with the arrow down key and hit enter to select it.
Any other helpful tips for drop down lists and VBA would be great!
我在 excel 中有一些数据验证下拉列表，我可以浏览所有列表，但我必须按 alt + 向下箭头来显示列表，他们是一种可以在我专注于选项卡时自动显示的方式。
因此，在下拉列表的焦点上，我希望该列表出现，以便我可以使用向下箭头键选择它，然后按 Enter 选择它。
任何其他有关下拉列表和 VBA 的有用提示都会很棒！
采纳答案by Henrik K
edit: still using VBA send keys.
编辑：仍在使用 VBA 发送密钥。
On the sheet where the data validation cell resides (assumed it is cells A1:C1 on Sheet1), put in the following code in the Microsoft Excel Sheet1 Module (the module that holds the VBA code that is related to the first sheet)
在数据验证单元格所在的工作表上（假设它是 Sheet1 上的单元格 A1:C1），在 Microsoft Excel Sheet1 模块（保存与第一张工作表相关的 VBA 代码的模块）中放入以下代码
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo Err1:
If Target = Range("A1") Then
Application.SendKeys ("%{UP}")
End If
If Target = Range("B1") Then
Application.SendKeys ("%{UP}")
End If
If Target = Range("C1") Then
Application.SendKeys ("%{UP}")
End If
Err1:
'do nothing
End Sub
回答by SDB MadDog
I found this helpful but would like to pass on a couple of observations.
我发现这很有帮助，但想传递一些观察结果。
Using
If Target.Cells.Count = 1 Then
If Target.Validation.InCellDropdown = True Then
使用
如果 Target.Cells.Count = 1 那么
如果 Target.Validation.InCellDropdown = True 那么
in the event will apply this to all validation drop lists on the sheet rather than listing the individual cells. You need the first if to avoid an error caused by selecting multiple cells.
事件将应用于工作表上的所有验证下拉列表，而不是列出单个单元格。您需要第一个 if 以避免因选择多个单元格而导致错误。
- Beware of send keys being called twice in a row. It turns your num lock off and I had to add an API call to turn it back on again.
- 当心发送键被连续调用两次。它关闭了您的 num 锁，我不得不添加一个 API 调用以再次打开它。
I hope this helps others
我希望这对其他人有帮助
