Excel '13 VBA级联ComboBox - 在Combobox2中获取唯一值时遇到问题 [英] Excel '13 VBA Cascading ComboBox - Trouble getting unique values in Combobox2

问题描述

我终于可以得到combobox2加载与combobox1中做出的选择对应的值。问题是，我不能只获得唯一的值来填充combobox2。它返回基于combobox1的选择包括重复的所有值。我已经将.clear移动到代码中的各个点，但只是更改它从加载多个重复值，显示1个总值或空白。

I finally was able to get combobox2 to load with values that correspond with the selection made in combobox1. The issue is that i can not get only unique values to populate in combobox2. It returns all of the values based on combobox1's selection including the duplicates. I have moved the .clear to various spots in the code but that only changes it from loading multiple duplicate values to showing 1 total value or blank.

以下是我修改
https://www.youtube.com/watch?v=yMO_wCZgQbc

（DATA）是工作表，其中我的数据是
（CHART）是工作表，其中我的ComboBoxes是
cmbRent = ComboBox1
cmbSub = ComboBox2

("DATA") is the worksheet where my data is ("CHART") is the worksheet where my ComboBoxes are cmbRent = ComboBox1 cmbSub = ComboBox2

    Private Sub cmbRent_Change()

MyVal = Me.cmbRent.Value

'loop thru col B
lr = ThisWorkbook.Sheets("DATA").Cells(Rows.Count, 1).End(xlUp).Row

  'clear cmbSub
    ThisWorkbook.Sheets("CHART").cmbSub.Clear


'loop thru
For x = 2 To lr
    If MyVal = ThisWorkbook.Sheets("DATA").Cells(x, 1) Then
        'add to combobox
   ThisWorkbook.Sheets("CHART").cmbSub.AddItem ThisWorkbook.Sheets("DATA").Cells(x, 2)
  End If

Next x


      ThisWorkbook.Sheets("CHART").cmbSub.ListIndex = -1
End Sub

推荐答案

您需要添加一个检查，以查看这些是否已经添加到组合框。
我还使用工作表的变量，以方便代码可读性，并使其更快地输入。

You need to add a check to see if these have already been added to the combobox. I've also used variables for the worksheets for ease of code readability and to make it faster to type.

Dim wsChart As Worksheet
Dim wsData As Worksheet
Dim listOfValues As String 'To store list of values already added
Dim ValueToAdd As String 'To store new value to add
listOfValues = ""
Set wsChart = ThisWorkbook.Sheets("CHART") 
Set wsData = ThisWOrkbook.Sheets("DATA")

.....(insert rest of code here)

For x = 2 To lr
    If MyVal = wsData.Cells(x, 1) Then
       'add to combobox
        ValueToAdd = wsData.Cells(x,2) 'Get value from worksheet
        If InStr(listOfValues, valueToAdd) = 0 Then
        'Check to see if the value has already been added
        'If not, add to values added and add the item to the combobox.
              listOfValues = listOfValues & ValueToAdd
              wsChart.cmbSub.AddItem valueToAdd
        End If
    End If
Next x

这篇关于Excel '13 VBA级联ComboBox - 在Combobox2中获取唯一值时遇到问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！