用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 单元格, 发送弹出列表框的指令

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

赞 (0)

相关推荐