Excel验证下拉列表使用VBA

问题描述

我有一个值数组。我想使用VBA在Excel单元格中显示这些值作为下拉列表。

这是我的代码。它显示类型不匹配错误！

  Dim xlValidateList（6）As Integer 
 xlValidateList（1）= 1 
 xlValidateList（2）= 2 
 xlValidateList（3）= 3 
 xlValidateList（4）= 4 
 xlValidateList（5）= 5 
 xlValidateList（6）= 6 
 
带范围（A1）验证
 .Add类型：= xlValidateList，AlertStyle：= xlValidAlertStop，Operator：= _ 
 xlBetween，Formula1 ：= ValidationList 
 .IgnoreBlank = True 
 .InCellDropdown = True 
 .InputTitle =
 .ErrorTitle =
 .InputMessage =
 .ErrorMessage =
 .ShowInput = True 
 .ShowError = True 
结束
  

问题发生在以下行...

  .Add类型：= xlValidateList，AlertStyle：= xlValidAlertStop ，运算符：= _ 
  

请让我在哪里问题是...提前感谢

解决方案

您正在将数组定义为 xlValidateList（），所以当你尝试分配类型时，它会混淆你想要分配给什么类型。

而是尝试这样做： / p>

  Dim MyList（5）As String 
 MyList（0）= 1 
 MyList（1）= 2 
 MyList（2）= 3 
 MyList（3）= 4 
 MyList（4）= 5 
 MyList（5）= 6 
 
带范围（A1）。验证
。删除
 .Add类型：= xlValidateList，AlertStyle：= xlValidAlertStop，_ 
运算符：= xlBetween，Formula1：= Join（MyList，，） 
结束
  

I have an array of values. I want to show those values in Excel Cell as drop down list using VBA.

Here is my code. It shows "Type Mismatch Error!"

Dim xlValidateList(6) As Integer
xlValidateList(1) = 1
xlValidateList(2) = 2
xlValidateList(3) = 3
xlValidateList(4) = 4
xlValidateList(5) = 5
xlValidateList(6) = 6

With Range("A1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValidationList
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

The problem occurs in following line...

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

Please let me where the problem is... Thanks in advance

解决方案

You are defining your array as xlValidateList(), so when you try to assign the type, it gets confused as to what you are trying to assign to the type.

Instead, try this:

Dim MyList(5) As String
MyList(0) = 1
MyList(1) = 2
MyList(2) = 3
MyList(3) = 4
MyList(4) = 5
MyList(5) = 6

With Range("A1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(MyList, ",")
End With

这篇关于Excel验证下拉列表使用VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！