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

问题描述

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

解决方案

在数据验证列表中，您可以使用这3种方案。

A）在同一个表格中引用范围的公式

B）使用逗号直接输入的列表

C）指向相同/另一个表单的命名范围的公式

现在我们需要照顾所有为了访问数据验证单元列表，您必须使用 Rng.Validation

查看此代码。

  Sub Sample（）
 Dim ws As Worksheet 
 Dim dvRng As Range，rng As Range 
 Dim strList As String 
 Dim MyAr（）As String 
 
设置ws = ThisWorkbook.Sheets（Sheet1 ）
设置dvRng = ws.Range（A1）'案例A 
'设置dvRng = ws.Range（C1）'案例B 
'设置dvRng = ws.Range （E1）'Case C 
 
'~~>在数据验证中获取公式
 strList = dvRng.Validation.Formula1 
 
'~~>检查它是否具有案例A和案例C的符号
如果InStr（1，strList，=）然后
 strList =替换（strList，=，）
设置rng =范围（strList）
 rng.Copy Sheet2.Range（A1）
 Else 
'~~> case B 
如果InStr（1，strList，，）然后
 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 
  

我已经评论过代码，所以你不应该面对任何问题。仍然如果你这样做只是问：）

Working on an existing spreadsheet which contains two dropdown lists. Is it possible to export or edit data from these drop lists?

解决方案

In a Data Validation list, you can have these 3 scenarios.

A) A formula which refers to a range in the same sheet

B) A List which is directly typed using commas

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.

To access the list of a Data Validation Cell, you have to use the Rng.Validation.Formula1

See this code.

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屋！