以编程方式将下拉列表添加到特定单元格 [英] Programmatically add a drop down list to a specific cell

问题描述

我想知道如何使用VBA以编程方式将下拉列表添加到Excel工作表的特定单元格，例如，我想能够将下拉列表添加到cells(i，j)，并且定义列表的元素.

I would like to know how I can programmatically add a drop down list to a specific cell of an Excel worksheet using VBA, I would like to be able to add a drop down list to cells(i,j) for example and to define the elements of the list.

推荐答案

以编程方式进行:

With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Value1;Value2;Value3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Formula1在列表中以;分隔的值.

Where Formula1 has the values in a list separated by ;.

如果您有一个要在下拉列表中填充的动态记录列表，请使用以下公式定义一个命名范围:

If you have a dynamic list of records that you want to populate in a drop down then, define a named range using the following formula:

=OFFSET(Sheet1!$A$1;1;0;COUNTA(Sheet1!$A:$A)-1)

..假设您的数据位于Sheet1中，并且在第一行中有一个标题:

..assuming that your data is in Sheet1 with a header in the first row:

A1  Header
A2  Value1
A2  Value2
A3  Value3

这篇关于以编程方式将下拉列表添加到特定单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！