用VBA代码轻松控制下拉列表

本技巧的目的：掌握创建数据有效性，判断是否存在数据有效性，根据当前选择和数据创建动态数据有效性。

• 如下图的动态数据有效性：

  

• 

1. 使用代码助手

• 代码助手下载地址 http://excel880.com/blog/archives/11297

• 使用代码助手，输入中文：数据有效性 + 空格， 可以弹出选择框，选择需要的代码，实现代码的快速输入。

• 

• 代码助手可以随时收录需要的代码，方便下次使用。

2. 创建数据有效性的方法

• 通过录制宏可以得到基础代码：

• 

• 注释：

• Validation: 数据有效性

• Delete: 删除

• Add: 添加数据有效性验证

• Type必选有效性验证类型:

• xlValidateCustom:必需有 Formula1: 必须有一个表达式，忽略 Formula2。数据项有效时 True，数据项无效时 False

• xlInputOnly: 使用 AlertStyle、Formula1 或 Formula2。

• xlValidateList: 必需有Formula1: 必须包含以逗号分隔的值列表，或对该列表的工作表引用。忽略 Formula2

• xlValidateWholeNumber、xlValidateDate、xlValidateDecimal、xlValidateTextLength 或 xlValidateTime

• 必须指定 Formula1 或 Formula2 之一，或两者均指定。

• AlertStyle可选有效性验证警告的样式。

• xlValidAlertInformation：信息图标为标题的样式

• xlValidAlertStop：停止图标为标题的样式

• xlValidAlertWarning：警告图标为标题的样式

• Operator可选数据有效性验证运算符

• 

• Formula1可选数据有效性验证等式中的第一部分。

• Formula2可选当 Operator 为 xlBetween 或 xlNotBetween 时，数据有效性验证等式的第二部分（其他情况下，此参数被忽略）。

• IgnoreBlank：数据有效性检验是否允许空值，允许: True 不允许：False

• InCellDropdown：是否含取值的下拉列表，是：True 否：False

• InputTitle：设置数据有效性输入对话框的标题

• ErrorTitle：设置数据有效性错误对话框的标题

• InputMessage：设置数据有效性检验输入信息

• ErrorMessage：设置数据有效性检验错误消息

• IMEMode：设置日文输入规则的说明

• 

• ShowInput在数据有效性检查区域内选定了某一单元格时，显示数据有效性检查输入消息，为 True

• ShowError输入无效数据时显示数据有效性检查错误消息，为 True

• 修改代码为：

• 

• With [A1:A10].Validation '设置 A1:A10的数据有效性

• .Delete '删除原来的

• .Add Type:=xlValidateList, Formula1:="1,2,3,4" '值列表类型，值：1,2,3,4

• End With

• 设置后：

• 

3. 判断单元格是否存在数据有效性的方法

• 

• 注释：

• type: 返回以下值，它代表区域的数据类型有效性验证。

• 

• On Error Resume Next '出现空值时会发生错误，加忽略错误语句

• 执行后：

• 检查 A11 单元格 结果：

• 

• 检查 A2 单元格：

• Sub 判断是否存在有效性()

•    Dim rng As Range

•    Set rng = [A2]

•    If 有效性判断(rng) Then '含有效性时

•           MsgBox "单元格" & rng.Address(0, 0) & ":存在有效性" '执行存在的代码

•     Else '不含有效性时

•           MsgBox "单元格" & rng.Address(0, 0) & ":不存在有效性" '执行不存在的代码

•      End If

• End Sub

• Function 有效性判断(r As Range)

•    Dim s

•    On Error Resume Next '忽略空值错误

•    s = r.Validation.Type '数据有效性返回值

•    If s <> "" Then '值不为空时

•         有效性判断 = True '有数据有效性设置

•    Else '值为空时

•         有效性判断 = False '无数据有效性设置

•    End If

• End Function

• ★★★注意：安装代码助手后，双击 TAB 可以实现自动排版功能，再也不用去手动对齐代码。

• 结果：

• 

4. 根据当前选择和数据创建动态数据有效性的方法

• 建立动态数据有效性需要用到二个事件：

• SelectionChange 当工作表上的选定区域发生改变时, Change 当用户更改工作表中的单元格

• 

• 事件中还使用了模块中的函数，方便事件的调试。

• 

• 注释：

• If InStr(strlist, rng.Value) = 0 Then '当传入的列表值参数与原有单元格的值不相符时

•      rng = "" '原有单元格值置空

• End If

• If rng = "" Then '如果原有单元格为空时

•    rng.Select '选中单元格

•    Application.SendKeys "%{down}" '发送弹出列表框的指令

• End If

• 在语句打断点调试该过程:

• 原来 A4 值为: 显示器 B4 值为: 显示器对应的 飞利浦15

  

• 当 A4 值 改变为: 主机时, 断点启动

• 

• 代码进入子函数过程: 添加2级数据有效性X Target.Offset(0, 1), "Z286,Z386,Z486,Z586"

• 

• 

• 传入的列表值参数与原有单元格的值不相符, B4 单元格 置空

• 

• 选中 B4 单元格, 发送弹出列表框的指令

• 

• 动态数据有效性建立完毕