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

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

• 

  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 单元格, 发送弹出列表框的指令
• 
• 动态数据有效性建立完毕

  更多作品请百度搜索 郑广学

  VBA代码助手 懒人神器 在 EXCEL880.COM 官网下载