用VBA代码动态生成Excel下拉菜单

用VBA代码动态生成Excel下拉菜单

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

  • 如下图的动态数据有效性:用VBA代码动态生成Excel下拉菜单
  • 用VBA代码动态生成Excel下拉菜单

1. 使用代码助手

  • 代码助手下载地址 http://excel880.com/blog/archives/11297
  • 使用代码助手,输入中文:数据有效性 + 空格, 可以弹出选择框,选择需要的代码,实现代码的快速输入。
  • 用VBA代码动态生成Excel下拉菜单
  • 代码助手可以随时收录需要的代码,方便下次使用。

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

  • 通过录制宏可以得到基础代码:
  • 用VBA代码动态生成Excel下拉菜单
  • 注释:
  • 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 可选 数据有效性验证运算符
  • 用VBA代码动态生成Excel下拉菜单
  • Formula1 可选 数据有效性验证等式中的第一部分。
  • Formula2 可选 当 Operator 为 xlBetween 或 xlNotBetween 时,数据有效性验证等式的第二部分(其他情况下,此参数被忽略)。
  • IgnoreBlank:数据有效性检验是否允许空值,允许: True 不允许:False
  • InCellDropdown:是否含取值的下拉列表,是:True 否:False
  • InputTitle:设置数据有效性输入对话框的标题
  • ErrorTitle:设置数据有效性错误对话框的标题
  • InputMessage:设置数据有效性检验输入信息
  • ErrorMessage:设置数据有效性检验错误消息
  • IMEMode:设置日文输入规则的说明
  • 用VBA代码动态生成Excel下拉菜单
  • 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
  • 设置后:
  • 用VBA代码动态生成Excel下拉菜单

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

  • 用VBA代码动态生成Excel下拉菜单
  • 注释:
  • type: 返回以下值,它代表区域的数据类型有效性验证。
  • 用VBA代码动态生成Excel下拉菜单
  • On Error Resume Next ‘出现空值时会发生错误,加忽略错误语句
  • 执行后:
  • 检查 A11 单元格 结果:
  • 用VBA代码动态生成Excel下拉菜单
  • 检查 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 可以实现自动排版功能,再也不用去手动对齐代码。
  • 结果:
  • 用VBA代码动态生成Excel下拉菜单

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

  • 建立动态数据有效性需要用到二个事件:
  • SelectionChange 当工作表上的选定区域发生改变时, Change 当用户更改工作表中的单元格
  • 用VBA代码动态生成Excel下拉菜单
  • 事件中还使用了模块中的函数,方便事件的调试。
  • 用VBA代码动态生成Excel下拉菜单
  • 注释:
  • If InStr(strlist, rng.Value) = 0 Then ‘当传入的列表值参数与原有单元格的值不相符时
  •      rng = “” ‘原有单元格值置空
  • End If
  • If rng = “” Then ‘如果原有单元格为空时
  •    rng.Select ‘选中单元格
  •    Application.SendKeys “%{down}” ‘发送弹出列表框的指令
  • End If
  • 在语句打断点调试该过程:
  • 原来 A4 值为: 显示器 B4 值为: 显示器对应的 飞利浦15用VBA代码动态生成Excel下拉菜单
  • 当 A4 值 改变为: 主机时, 断点启动
  • 用VBA代码动态生成Excel下拉菜单
  • 代码进入子函数过程: 添加2级数据有效性X Target.Offset(0, 1), “Z286,Z386,Z486,Z586”
  • 用VBA代码动态生成Excel下拉菜单
  • 用VBA代码动态生成Excel下拉菜单
  • 传入的列表值参数与原有单元格的值不相符, B4 单元格 置空
  • 用VBA代码动态生成Excel下拉菜单
  • 选中 B4 单元格, 发送弹出列表框的指令
  • 用VBA代码动态生成Excel下拉菜单
  • 动态数据有效性建立完毕
收藏 (0) 打赏

感谢您的支持,我会继续努力的!

打开微信/支付宝/QQ扫一扫,即可进行扫码打赏哦,分享从这里开始,精彩与您同在
点赞 (0)

1. 本站所有资源来源于用户上传和网络,均不允许转载,如有侵权请联系站长!
2. 分享目的仅供大家学习和交流,您必须在下载后24小时内删除!
3. 如发现会员转载本站资源文章,本站有权封禁会员账号!
4. 不得使用于非法商业用途,商用请购买正版,不得违反国家法律。否则后果自负!
5. 本站提供的源码、模板、插件等等其他资源,都不包含技术服务请大家谅解!
6. 如有链接无法下载、失效或广告,请联系管理员处理! 7. 本站资源售价只是赞助,收取费用仅维持本站的日常运营所需!

易生阁资源网 网络教程 用VBA代码动态生成Excel下拉菜单 https://www.yishengge.top/2256.html

常见问题
  • 本站所有源码、素材版权归原作者所有,新手源码提供的源码只能用于学习研究,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。
查看详情
  • 最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用谷歌浏览器。
查看详情

相关文章

用VBA代码动态生成Excel下拉菜单-海报

分享本文封面