Sendkeys打开并滚动下拉列表验证列表 [英] Sendkeys to open and scroll through a dropdown validation list

问题描述

 

我知道使用sendkeys不是VBA代码最稳定的选择，但我正在尝试编写一个循环来逐步完成一个drop顺序关闭验证列表。作为一个虚拟测试，我做了以下代码，看看我是否可以打开单元格H3中的菜单，将
前进到列表中的下一个条目，然后使用sendkeys选择它： 

子测试（）



   范围（"H3"）。选择

     Application.SendKeys"％{DOWN}"，True

    Application.SendKeys" {DOWN}"，True

     Application.SendKeys" {ENTER}"，True



结束次级

这将打开菜单，但之后不做任何事情。当菜单打开时，我已尝试将Wait和Sleep命令暂停，但这似乎不起作用。非常感谢任何帮助！

解决方案

Re： 验证列表内容



此代码显示包含验证列表的范围内容。

'---

Sub LoopValidationRange（）

  Dim N As Long

  Dim S As String

  Dim rng As Range

  S =范围（"H3"）。验证。公式1

 设置rng =范围（S）

  For N = 1至rng.Count

  '仅供测试 - 使用小清单！

   MsgBox rng（N）.Value

  Next

End Sub

'---



Jim Cone

美国俄勒冈州波特兰市
https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0

Hi, 

I know using sendkeys is not the most stable choice for VBA code, but I'm trying to write a loop that will step through a drop down validation list sequentially. As a dummy test, I made the following code to see if I could open a menu in cell H3, advance to the next entry in the list, and select it using sendkeys: 

Sub test()

    Range("H3").Select
    Application.SendKeys "%{DOWN}", True
    Application.SendKeys "{DOWN}", True
    Application.SendKeys "{ENTER}", True

End Sub

This will open the menu, but not do anything after that. I've tried putting Wait and Sleep commands in to pause when the menu is opening, but that didn't seem to work. Any help is greatly appreciated!

解决方案

Re:  validation list contents

This code displays the contents of the range that contains the valiation list.
'---
Sub LoopValidationRange()
 Dim N As Long
 Dim S As String
 Dim rng As Range
 S = Range("H3").Validation.Formula1
 Set rng = Range(S)
 For N = 1 To rng.Count
  'for testing only - use a small list!
   MsgBox rng(N).Value
 Next
End Sub
'---

Jim Cone
Portland, Oregon USA
https://www.dropbox.com/sh/ttybwg5e9r31twa/AAAnyBTHPX5XsTDp10ItTcw4a?dl=0

这篇关于Sendkeys打开并滚动下拉列表验证列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！