问题描述
处理包含两个下拉列表的现有电子表格.是否可以从这些下拉列表中导出或编辑数据?
Working on an existing spreadsheet which contains two dropdown lists. Is it possible to export or edit data from these drop lists?
推荐答案
在数据验证列表中,您可以有这 3 个场景.
In a Data Validation list, you can have these 3 scenarios.
A) 引用同一工作表中某个范围的公式
A) A formula which refers to a range in the same sheet
B) 使用逗号直接输入的列表
B) A List which is directly typed using commas
C) 引用来自同一张/另一张纸的命名范围的公式
C) A formula which refers to a named range from same/another sheet
现在我们需要满足所有三种情况,以防我们想要检索该列表.
Now we need to cater to all the three scenarios in case we want to retrieve that list.
要访问数据验证单元的列表,您必须使用 Rng.Validation.Formula1
To access the list of a Data Validation Cell, you have to use the Rng.Validation.Formula1
查看此代码.
Sub Sample()
Dim ws As Worksheet
Dim dvRng As Range, rng As Range
Dim strList As String
Dim MyAr() As String
Set ws = ThisWorkbook.Sheets("Sheet1")
Set dvRng = ws.Range("A1") ' Case A
'Set dvRng = ws.Range("C1") ' Case B
'Set dvRng = ws.Range("E1") ' Case C
'~~> Get the formula in the data validation
strList = dvRng.Validation.Formula1
'~~> Check if it has an = sign for Case A and Case C
If InStr(1, strList, "=") Then
strList = Replace(strList, "=", "")
Set rng = Range(strList)
rng.Copy Sheet2.Range("A1")
Else
'~~> Case B
If InStr(1, strList, ",") Then
MyAr = Split(strList, ",")
Sheet2.Range("A1:A" & UBound(MyAr) + 1).Resize.Value = Application.Transpose(MyAr)
Else
Sheet2.Range("A1").Value = strList
End If
End If
End Sub
我已经对代码进行了注释,因此您应该不会遇到任何问题.尽管如此,如果你这样做,那么简单地问:)
I have commented the code so you shouldn't face any problems. Still if you do then simply ask :)
这篇关于在 Excel 中编辑或导出下拉列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!