作者：绿色风
分类：
时间：2022-08-17
浏览：101
楼主
水星钓鱼 |
Q：用数据有效性设置了下拉菜单后，当输入了不在菜单的数据时如何能自动将该值加入到菜单中？
A：首先必须将所有数据有效性的出错警告改为“信息”，然后在需要执行这样操作的工作表模块代码中输入以下代码：- Private Sub Worksheet_Change(ByVal Target As Range)
- Dim arr, temp
- Dim i As Integer
- On Error GoTo solution
- With Target.Validation
- If .Type = xlValidateList Then
- If .Formula1 Like "=*" Then
- arr = Application.Evaluate(.Formula1)
- If UBound(arr, 1) = 1 Then
- arr = Application.WorksheetFunction.Index(arr, 1, 0)
- temp = Filter(arr, Target)
- If UBound(temp) < LBound(temp) Then
- i = UBound(arr) + 1
- ReDim Preserve arr(1 To i)
- arr(i) = Target
- Target.Validation.Modify Type:=xlValidateList, Formula1:=Join(arr, ",")
- End If
- Else
- arr = Application.WorksheetFunction.Transpose(arr)
- temp = Filter(arr, Target)
- If UBound(temp) < LBound(temp) Then
- i = UBound(arr) + 1
- ReDim Preserve arr(1 To i)
- arr(i) = Target
- Target.Validation.Modify Type:=xlValidateList, Formula1:=Join(arr, ",")
- End If
- End If
- Else
- arr = Split(.Formula1, ",")
- temp = Filter(arr, Target)
- If UBound(temp) < LBound(temp) Then
- i = UBound(arr) + 1
- ReDim Preserve arr(0 To i)
- arr(i) = Target
- Target.Validation.Modify Type:=xlValidateList, Formula1:=Join(arr, ",")
- End If
- End If
- End If
- End With
- Exit Sub
- solution:
- Exit Sub
- End Sub
以上代码分别判断了数据有效性下拉列表的三种数据源，分别为垂直的单元格区域、水平的单元格区域、手动输入的用逗号分隔的列表项，然后分别对这些数据源提取列表项转化为数组，最后用数组作为下拉列表的数据源。
附件如下：
 数据有效性.rar
|
2楼
rongjun |
学习了
 |
3楼
飞虎 |
学习了一下 |
4楼
WDZ |
学习了
|
5楼
老糊涂 |
|
免责声明
有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素，
经与ExcelTip.Net站长Apolloh商议并征得其同意，
现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示，
供有需要的人士查询使用，也慰缅曾经的论坛时代。
所示各个帖子的原作者如对版权有异议，
可与本人沟通提出，或于本站点留言，我们会尽快处理。
在此，感谢ExcelTip.Net站长Apolloh的支持，感谢本站点所有人**绿色风（QQ:79664738)**的支持与奉献，特此鸣谢！
------本人网名**KevinChengCW（QQ:1210618015)**原ExcelTip.Net总版主之一