如何在Excel 2016中使用VBA创建复杂的从属下拉列表？ [英] How to create complex dependent dropdown using VBA in Excel 2016?

问题描述

我在名为零件的工作表中有以下数据。

I have the following data in a worksheet called Parts.

在一个名为Planning的不同的工作表中，我有以下数据：

In a different worksheet called Planning, I have the following data:

在上面的计划工作表中，单元格D3是一个下拉列表，允许您选择显示语言。当前的选择是英语和日语。 A列的单元格也是允许选择维的下拉列表。

In the above Planning Worksheet, Cell D3 is a dropdown that allow to choose a display language. The current choices are "English" and "Japanese". The A column's cells are also dropdown that allow to select a dimension.

我要创建的下拉列表是：

What I want to do is create a dropdown that:

1. 取决于A列中的单元格。 应根据相应的A单元格的值从零件工作表中过滤数据。


2. 还取决于D3单元格。如果D3为英语，则下拉列表应显示英语描述；如果D3为日语，则下拉列表<日语>


3. 一旦选中，下拉菜单应为零件而非说明。换句话说，它的行为应类似于HTML中的选择标记。

1. is dependent on the cell in the A column. The dropdown should filtered data from the Parts Worksheet according to the value of the corresponding A cell.
2. is also dependent on the D3 Cell. The dropdown should display "English description" if D3 is "English" or "Japanese description" if D3 is "Japanese"
3. once selected, the data in the dropdown should be the Part and not the description. In other words, it should behave like a select tag in HTML.

我是VBA的新手，经过大量搜索之后，我不知道该怎么做。我非常希望得到详细的答案。

I am new to VBA and after quite a lot of searching, I can't figure out how to do this. I would really appreciate a detailed answer. Thank you in advance!

编辑：

最终零件工作表的长度至少为10,000行。用户无法手动创建命名列表。因此，我认为我应该使用VBA。

The final Parts Worksheet will be at least 10,000 lines long. The user can't create named list manually. For this reason, I think I should use VBA.

推荐答案

我不确定在将其发布为时是否尝试过此操作

I'm not sure if you tried this when I posted it as an answer on your question from yesterday.

该代码通过选择时根据A列中的值即时创建一个验证下拉列表，来完成您所需的一切B列中的单元格。下拉菜单根据语言显示产品代码和说明。一旦选择了产品代码，就删除了描述，并从单元格中删除了验证。

The code does everything that you need by creating a validation dropdown on-the-fly based on the value in column A when you select a cell on column B. The dropdown displays the product code and the description depending on the language. The description is removed once a product code has been selected and the validation is removed from the cell.

虽然代码可以完成您所需的所有操作，但它并不完美，但是可以为您提供一个巨大的开端，并且可以与您的工作表名称一起使用，如果您复制粘贴，

While the code does do everything that you require it's not perfect but it gives you a huge head start and it should work with your sheet names etc. if you copy and paste it and give it a try.

Dim CHANGING_VAL As Boolean 'Global Variable that can be set to prevent the onchange being fired when the Macro is removing the description from the dropdown.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


    If Target.Column = 2 And CHANGING_VAL = False Then
        CHANGING_VAL = True
        If InStr(1, Target.Value, "~") > 2 Then
            Target.Value = Left(Target.Value, InStr(1, Target.Value, "~") - 2)
        End If
        Target.Validation.Delete
        Target.Font.Color = RGB(0, 0, 255)
        CHANGING_VAL = False
    End If

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Column = 2 Then
        If Target.Offset(0, -1) <> "" Then
            strValidList = ""
            For intRow = 1 To 10000
                If Sheets("Parts").Cells(intRow, 1) = Target.Offset(0, -1) Then
                    If Sheets(Target.Parent.Name).Cells(3, 4) = "English" Then
                        strValidList = strValidList & Sheets("Parts").Cells(intRow, 2) & " ~ " & Sheets("Parts").Cells(intRow, 3) & ", "
                    Else
                        strValidList = strValidList & Sheets("Parts").Cells(intRow, 2) & " ~ " & Sheets("Parts").Cells(intRow, 4) & ", "
                    End If
                End If
            Next

            If strValidList <> "" Then
                strValidList = Left(strValidList, Len(strValidList) - 2)

                Target.Select

                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=strValidList
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
            End If
        End If
    Else
        Sheets(Target.Parent.Name).Range("B:B").Validation.Delete
    End If

End Sub

这篇关于如何在Excel 2016中使用VBA创建复杂的从属下拉列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！