我想要实现的是Cascading或Dependent ComboBoxes，并且在我帮助下我终于取得了所有4个成功。
ComboBox1 = Category ComboBox2 = Sub Category ComboBox3 = Location (unique to chosen subcategory) ComboBox4 = Customer (unique to chosen subcategory and location)正在发生的事情是在comboBox4中，所选位置的所有客户都填充了combobox4而不是所选位置的所有客户，这些客户也与子类别一致。
ComboBox1 = cmbRent ComboBox2 = cmbSub ComboBox3 = cmbLoc ComboBox4 = cmbCust我的所有代码都位于工作表“CHART”上。 我的所有数据都位于工作表“DATA”上我所有的ComboBox都位于“CHART”
正在引用的数据按框的顺序分为4列。
Column1 = Category Column2 = Sub Category Column3 = Location Column4 = Customer我觉得我需要在cmbSub和cmbLoc中引用Selection以实现我想要的目标？
以下是我应用于工作表的所有组合框代码
Private Sub cmbRent_Change() 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") 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 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, wsData.Cells(x, 2)) = 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 Me.cmbSub.AddItem ValueToAdd End If End If Next x ThisWorkbook.Sheets("CHART").cmbSub.ListIndex = -1 End Sub Private Sub cmbSub_Change() 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") MyVal = ThisWorkbook.Sheets("CHART").cmbSub.Value 'loop thru col c lr = wsData.Cells(Rows.Count, 2).End(xlUp).Row ThisWorkbook.Sheets("CHART").cmbLoc.Clear For x = 2 To lr If MyVal = wsData.Cells(x, 2) Then 'add to combobox ValueToAdd = wsData.Cells(x, 3) 'Get value from worksheet If InStr(listOfValues, wsData.Cells(x, 3)) = 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 ThisWorkbook.Sheets("CHART").cmbLoc.AddItem ValueToAdd End If End If Next x ThisWorkbook.Sheets("CHART").cmbLoc.ListIndex = -1 End Sub Private Sub cmbLoc_Change() 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") MyVal = ThisWorkbook.Sheets("CHART").cmbLoc.Value 'loop thru col D lr = wsData.Cells(Rows.Count, 3).End(xlUp).Row ThisWorkbook.Sheets("CHART").cmbCust.Clear For x = 2 To lr If MyVal = wsData.Cells(x, 3) Then 'add to combobox ValueToAdd = wsData.Cells(x, 4) 'Get value from worksheet If InStr(listOfValues, wsData.Cells(x, 4)) = 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 ThisWorkbook.Sheets("CHART").cmbCust.AddItem ValueToAdd End If End If Next x ThisWorkbook.Sheets("CHART").cmbCust.ListIndex = -1 End Sub如果您想了解更多背景信息，请查看此链接： Excel '13 VBA Cascading ComboBox -无法在Combobox2中获取独特的值
What I am trying to achieve is Cascading or Dependent ComboBoxes and with help I have finally had success with all 4.
ComboBox1 = Category ComboBox2 = Sub Category ComboBox3 = Location (unique to chosen subcategory) ComboBox4 = Customer (unique to chosen subcategory and location)What is occurring is in comboBox4 all of the customers for the selected Location are populating combobox4 instead of all of the customers for the selected location that also coincide with the subcategory.
ComboBox1 = cmbRent ComboBox2 = cmbSub ComboBox3 = cmbLoc ComboBox4 = cmbCustAll of my codes which are located on the worksheet "CHART". All of my data is located on the worksheet "DATA" All of my ComboBoxes are located "CHART"
The data that is being referenced is in 4 columns in the order that the boxes are.
Column1 = Category Column2 = Sub Category Column3 = Location Column4 = CustomerI feel like I need to be referenceing the Selection in cmbSub and cmbLoc in order to achieve what I want?
Here are all of my combobox codes that are applied to the worksheet
Private Sub cmbRent_Change() 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") 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 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, wsData.Cells(x, 2)) = 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 Me.cmbSub.AddItem ValueToAdd End If End If Next x ThisWorkbook.Sheets("CHART").cmbSub.ListIndex = -1 End Sub Private Sub cmbSub_Change() 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") MyVal = ThisWorkbook.Sheets("CHART").cmbSub.Value 'loop thru col c lr = wsData.Cells(Rows.Count, 2).End(xlUp).Row ThisWorkbook.Sheets("CHART").cmbLoc.Clear For x = 2 To lr If MyVal = wsData.Cells(x, 2) Then 'add to combobox ValueToAdd = wsData.Cells(x, 3) 'Get value from worksheet If InStr(listOfValues, wsData.Cells(x, 3)) = 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 ThisWorkbook.Sheets("CHART").cmbLoc.AddItem ValueToAdd End If End If Next x ThisWorkbook.Sheets("CHART").cmbLoc.ListIndex = -1 End Sub Private Sub cmbLoc_Change() 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") MyVal = ThisWorkbook.Sheets("CHART").cmbLoc.Value 'loop thru col D lr = wsData.Cells(Rows.Count, 3).End(xlUp).Row ThisWorkbook.Sheets("CHART").cmbCust.Clear For x = 2 To lr If MyVal = wsData.Cells(x, 3) Then 'add to combobox ValueToAdd = wsData.Cells(x, 4) 'Get value from worksheet If InStr(listOfValues, wsData.Cells(x, 4)) = 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 ThisWorkbook.Sheets("CHART").cmbCust.AddItem ValueToAdd End If End If Next x ThisWorkbook.Sheets("CHART").cmbCust.ListIndex = -1 End SubIf you would like some more background, please view this link: Excel '13 VBA Cascading ComboBox - Trouble getting unique values in Combobox2
最满意答案
问题是您没有对代码中的子类别进行比较。
您遇到的一个更大的问题是您似乎不了解代码正在做什么。 我会花一些时间来浏览你的代码并尝试理解每一行的作用。 可能会再次观看您在其他帖子中引用的视频。
你的代码部分正在检查要放入combobox4的值，也就是cmbCust，这里是：
If MyVal = wsData.Cells(x, 3) Then这是检查MyVal，以前定义为：
MyVal = ThisWorkbook.Sheets("CHART").cmbLoc.Value这只是cmbLoc中的选择，它对应于位置，但不包括子类别。
你需要做两次检查，我会修改变量名，以便它们更清晰。
Dim LocVal As String Dim SubCatVal As String ....more code here LocVal = ThisWorkbook.Sheets("CHART").cmbLoc.Value SubCatVal = ThisWorkbook.Sheets("CHART").cmbSub.Value ....more code here 'Now do the comparison If LocVal = wsData.Cells(x, 3) And SubCatVal = wsData.Cells(x,2) Then ValueToAdd = wsData.Cells(x, 4) .....Rest of code in the if statementThe problem is that you aren't doing a comparison for the subcategory in your code.
A bigger issue that you are having is that you don't seem to understand what the code is doing. I would take some time to walk through your code and try to understand what every line is doing. Possibly watch the video that you referenced in one of your other posts again.
The part of your code that is checking which values to put into combobox4, aka cmbCust is here:
If MyVal = wsData.Cells(x, 3) ThenThis is checking MyVal, which has previously been defined as:
MyVal = ThisWorkbook.Sheets("CHART").cmbLoc.ValueThis is only the selection in the cmbLoc, which corresponds to the location, but doesn't include the subcategory.
You need to do two checks, and I'd fix the variable names so that they are more clear.
Dim LocVal As String Dim SubCatVal As String ....more code here LocVal = ThisWorkbook.Sheets("CHART").cmbLoc.Value SubCatVal = ThisWorkbook.Sheets("CHART").cmbSub.Value ....more code here 'Now do the comparison If LocVal = wsData.Cells(x, 3) And SubCatVal = wsData.Cells(x,2) Then ValueToAdd = wsData.Cells(x, 4) .....Rest of code in the if statement更多推荐
发布评论