在“数据验证”下拉列表中设置默认值

问题描述

我在表格中有很多下拉列表，我已经通过菜单设置了数据> 数据验证 > 列表。在VBA中有大量的方法将它们全部设置为默认值吗？默认值是指列表中声明的第一个值。

BTW。我在A1中设置一个下拉列表的来源作为范围：

  = OFFSET（C1; 0; 0; 1; COUNTA（C1：Z1））
  

有没有办法把这个公式放在B1和在下拉列表源中将其调用为 = INDIRECT（B1）仅指向一个单元格？ （这不起作用）。

解决方案

我的解决方案是基于单元格样式。

（1）为具有下拉列表的单元格添加新样式。我命名为下拉。

（2）申请您将要下拉列表的单元格的样式。这里是B2。

（3）为您的下拉列表设置验证项目。我把它们放在单元格E2：G2中。

（4 Source：
= OFFSET（$ E2; 0; 0; 1; COUNTA（$ E2：$ K2））
，或者替代方案是声明一个命名范围，因为它是愚蠢的建议。这是非常有用的。在我的例子中，我可以在H2中添加一个新的验证项，它将被自动包含在验证列表中。 （5）当您要将所有具有样式的单元格设置为默认值时，运行一个宏。在我的情况下，默认值为' - 从列表中选择 - 。 此值将被设置，即使它不在验证列表中。

  Sub DropDownListToDefault ）
 Dim oCell As Range 
 Dim ACell As Range 
 Set ACell = ActiveCell 
 
对于每个oCell在ActiveSheet.UsedRange.Cells 
如果oCell.Style =Dropdown然后
 oCell.Value =' - 从列表中选择 - 
结束如果
下一个
 
 ACell.Select 
 End Sub 
  

运行宏后，您将有以下选择：

如果您希望用户从下拉列表中选择某项内容，则在进一步处理中很容易接受默认值。

I have many drop-down lists in my sheet which I have set up by menu Data > Data Validation > list. Is there a bulk way in VBA to set them all to default values? By default values I mean the first value declared in a list.

BTW. I set up a source for a dropdown list in A1 as a range by formula:

=OFFSET(C1;0;0;1;COUNTA(C1:Z1))

Would there be a way to put this formula in B1 and call it in the dropdown list source as =INDIRECT(B1) pointing only one cell? (This doesn't work).

解决方案

My solution idea is based on a Style of a cell.

(1) Add a new style for cells that will have dropdown lists. I named it "Dropdown".

(2) Apply the style for the cells that you are going to have dropdown lists. Here it is B2.

(3) Set up validation items for your dropdown list. I put them in cells E2:G2.

(4) If you want to have dynamic validation list put in Data > Data Validation > Source:=OFFSET($E2;0;0;1;COUNTA($E2:$K2))or alternatively declare a named range for that as Aprillion advised. It is very useful. In my example I can add a new validation item in H2 and it will be automatically included in the validation list.

(5) Run a macro when you want to set up all the cells with a style Dropdown to default value. In my case default value is '- Choose from the list -. This value will be set up even if it is not on the Validation list.

Sub DropDownListToDefault()
    Dim oCell As Range
    Dim ACell As Range
    Set ACell = ActiveCell

    For Each oCell In ActiveSheet.UsedRange.Cells
        If oCell.Style = "Dropdown" Then
            oCell.Value = "'- Choose from the list -"
        End If
    Next

    ACell.Select
End Sub

After running the macro you will have the following choice:

If you want a user to choose something from the dropdown list it is very easy not to accept the default value in further processing.

这篇关于在“数据验证”下拉列表中设置默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！