在 Excel 中编辑或导出下拉列表 [英] Edit or export drop down lists in Excel

问题描述

处理包含两个下拉列表的现有电子表格.是否可以从这些下拉列表中导出或编辑数据?

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 中编辑或导出下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！