带有条件数据的下拉列表 [英] Drop-down Lists with Conditional Data

问题描述

我有三个单元格区域(来自工作簿的另一张表)，用于存储要在下拉列表(窗体控件)列表上显示的值.我需要宏代码来重置单元格范围，或者需要一个命名范围来基于其他单元格值来定义下拉列表中的选项. . 到目前为止，我已经尝试了以下代码，但均未成功:

i have three ranges of cells (from another sheet in my workbook) that store the values that i want to appear on the drop-down (Form Control) list. i need the macro code to reset a cell range, or a named range to define the options in a drop-down list based on other cell value. . i've tried the following code with no success so far:

Sub DropDown11_Change()

Sub DropDown11_Change()

如果Range("A1")= 1然后
(输入范围为sheet1 a1:a50)

If Range("A1") = 1 Then
(input range from sheet1 a1:a50)

ElseIf Range("A1")= 2然后
(输入范围为sheet2 a1:a50)

ElseIf Range("A1") = 2 Then
(input range from sheet2 a1:a50)

ElseIf Range("A1")= 3然后
(输入范围为sheet3 a1:a50)

ElseIf Range("A1") = 3 Then
(input range from sheet3 a1:a50)

如果结束
结束

End If
End Sub

有什么建议吗?

谢谢

推荐答案

这应该可以帮助您将其中一个放置在标准模块(例如Module1)中.

This should help you out, place either of these in a standard Module (e.g., Module1).

Sub DropDown1_Change()

    Dim ddFillRange As String

    If Sheet1.Range("A1") = 1 Then
        ddFillRange = "Sheet1!A1:A50"
    ElseIf Sheet1.Range("A1") = 2 Then
        ddFillRange = "Sheet2!A1:A50"
    ElseIf Sheet1.Range("A1") = 3 Then
        ddFillRange = "Sheet3!A1:A50"
    End If

    Sheet1.Shapes("Drop Down 1").ControlFormat.ListFillRange = ddFillRange

End Sub

如果要使用命名范围，可以使用:

If you want to use named ranges, you can use:

Sub DropDown1_Change()

    Dim ddFillRange As String

    If Sheet1.Range("A1") = 1 Then
        ddFillRange = Range("NamedRange1").Name
    ElseIf Sheet1.Range("A1") = 2 Then
        ddFillRange = Range("NamedRange2").Name
    ElseIf Sheet1.Range("A1") = 3 Then
        ddFillRange = Range("NamedRange3").Name
    End If

    Sheet1.Shapes("Drop Down 1").ControlFormat.ListFillRange = ddFillRange

End Sub

然后，要使这些变化在单元格A1发生变化时，您需要将以下内容放置在Sheet1的模块中(在VBA编辑器窗口中右键单击Sheet1时，选择查看代码...请参见下图): >

Then, to have these fire upon cell A1 changing, you will need to place the following in Sheet1's module (when you right+click Sheet1 in the VBA Editor Window, select view code...see picture below):

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Range("A1")) Is Nothing Then DropDown1_Change
End Sub

这篇关于带有条件数据的下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！