Excel VBA将验证列表读入变量 [英] Excel VBA read validation list into variable

问题描述

我正在使用Excel 2010版本。

在下面的代码中，我使用变量qNR动态设置值，并显示在下拉菜单中。后来我需要在验证列表中读取值，删除或更新某些值并设置新值。

我找不到任何将验证列表读入变量的示例代码。 / p>

  With Selection.Validation 
 .Delete 
 .Add类型：= xlValidateList，AlertStyle：= xlValidAlertStop，运算符：= _ 
 xlBetween，Formula1：= qNR 
 .IgnoreBlank = True 
 .InCellDropdown = True 
 .InputTitle =
 .ErrorTitle =
 .InputMessage =
 .ErrorMessage =
 .ShowInput = True 
 .ShowError = True 
 
结束
  

谢谢！

解决方案

列表类型（验证类型3 ），然后 Debug.Print Range（yourRange）.Validation .Formula1 应该列出一个列表。所以 Object.Validation.Formula1 似乎适合你。请参阅这里获取更多信息。

I was trying to find an Excel VBA code that reads validation list into a variable.

I'm using Excel 2010 version.

In the code below I'm dynamically setting up values using variable qNR, and appears in dropdown menu. Later I need to read values in validation list, remove or update certain values and set new values.

I cannot find any example code that reads validation list into a variable.

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=qNR
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True

End With

Thank you!

解决方案

Assuming it's a list type (validation type 3), then Debug.Print Range(yourRange).Validation.Formula1 should print out a list. So Object.Validation.Formula1 would appear to work for you. See here for more info.

这篇关于Excel VBA将验证列表读入变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！