重新选择时清除级联ComboBox [英] Clear cascading ComboBox on reselection
问题描述
我正在使用多个ComboBox在excel中创建用户表单。第一个ComboBox列出了表格第1列中的值,而第二个ComboBox则列出了以下各列中的值。 ComboBox 2及更高版本也仅根据前面的框列出值。所有组合框仅显示唯一值。
I am in the process of creating a user form in excel that uses several ComboBox'. The first ComboBox lists values from column 1 of a table and the following ComboBox' lists values from the following columns. ComboBox 2 onwards also only lists values depending on the preceding box. All ComboBox' show unique values only.
这是我正在使用的当前代码:
Here is the current code I am using:
Option Explicit
Private Sub ComboBox1_Change()
Call cValues(ComboBox1.Value, ComboBox2, 2)
End Sub
Private Sub ComboBox2_Change()
Call cValues(ComboBox2.Value, ComboBox3, 3)
End Sub
Private Sub ComboBox3_Change()
Call cValues(ComboBox3.Value, ComboBox4, 4)
End Sub
Private Sub ComboBox4_Change()
Call cValues(ComboBox4.Value, ComboBox5, 5)
End Sub
Private Sub ComboBox5_Change()
Call cValues(ComboBox5.Value, ComboBox6, 6)
End Sub
Private Sub UserForm_Initialize()
Dim Rng As Range
Dim Dn As Range
Dim Dic As Object
With Sheets("Listuni")
Set Rng = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
End With
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
For Each Dn In Rng: Dic(Dn.Value) = Empty: Next
Me.ComboBox1.List = Application.Transpose(Dic.keys)
End Sub
Sub cValues(txt As String, Obj As Object, col As Integer)
Dim Dn As Range
Dim Rng As Range
Dim Dic As Object
With Sheets("Listuni")
Set Rng = .Range(.Cells(2, col), .Cells(Rows.Count, col).End(xlUp))
End With
Set Dic = CreateObject("Scripting.Dictionary")
Dic.CompareMode = 1
For Each Dn In Rng
If Dn.Offset(, -1).Value = txt Then
If Not Dic.exists(Dn.Value) Then
Dic(Dn.Value) = Empty
End If
End If
Next Dn
Obj.List = Application.Transpose(Dic.keys)
End Sub
我遇到的问题是用户重新选择时出现的前面的ComboBox。保留所有现有选择,而不是清除随后的框。
The problem I am having occurs when a user makes a reselection of a preceding ComboBox. Instead of clearing the subsequent boxes, all existing selections remain.
我正在寻找一种方法,每次重新选择前面的ComboBox时,都会清除/默认后续ComboBox的值。例如,如果我在组合框1和2中进行选择,然后在组合框1中更改选择,我希望清除组合框2而不是显示我先前的选择。请注意,启动时用户窗体的默认位置在任何ComboBox中均不显示任何值。
I am looking for a way to clear/default the values of subsequent ComboBox every time a reselection of a preceding ComboBox is made. For example if I make a selection in ComboBox 1 and 2 but then change my selection at ComboBox 1, I want ComboBox 2 to clear rather than show my previous selection. Note that the default position for the user form on launch shows no values in any ComboBox.
我尝试使用.clear方法进行更改,但是总是挂在以下位置:
I have tried using the .clear method on change however this always gets hung up at:
Obj.List = Application.Transpose(Dic.keys)
我怀疑这是因为清除从技术上来说是一种更改,因此它不能基于空值将值列表转换为其他框。
I suspect this is because a clear is technically a change and therefore it cannot transpose the list of values to other boxes based on a null value.
推荐答案
这会清除所有后续的ComboBox-如果Combo1更改,Combo2、3、4、5和6将被清除
This clears all subsequent ComboBoxes - if Combo1 changes, Combo2, 3, 4, 5, and 6 are cleared
Option Explicit
Private ws As Worksheet
Private d As Object
Private Sub UserForm_Initialize()
Dim cel As Range, txt As String, rng As Range
Set ws = Worksheets("Listuni")
Set d = CreateObject("Scripting.Dictionary"): d.CompareMode = vbTextCompare
Set rng = ws.Range(ws.Cells(2, 1), ws.Cells(ws.Rows.Count, 1).End(xlUp))
For Each cel In rng: d(cel.Value) = Empty: Next
ComboBox1.List = Application.Transpose(d.keys)
End Sub
Private Function setList(ByVal txt As String, ByRef cmb As ComboBox) As Object
Dim xID As Long, rng As Range, cel As Range, x As Control
xID = Right(cmb.Name, 1)
For Each x In Me.Controls
If TypeName(x) = "ComboBox" Then If Val(Right(x.Name, 1)) > xID - 1 Then x.Clear
Next
Set rng = ws.Range(ws.Cells(2, xID), ws.Cells(ws.Rows.Count, xID).End(xlUp))
d.RemoveAll
For Each cel In rng
If cel.Offset(, -1) = txt Then
If Not d.exists(cel.Value) Then
d(cel.Value) = Empty
End If
End If
Next
If d.Count > 0 Then cmb.List = Application.Transpose(d.keys) Else cmb.Clear
End Function
Private Sub ComboBox1_Change()
setList ComboBox1.Value, ComboBox2
End Sub
Private Sub ComboBox2_Change()
setList ComboBox2.Value, ComboBox3
End Sub
Private Sub ComboBox3_Change()
setList ComboBox3.Value, ComboBox4
End Sub
Private Sub ComboBox4_Change()
setList ComboBox4.Value, ComboBox5
End Sub
Private Sub ComboBox5_Change()
setList ComboBox5.Value, ComboBox6
End Sub
这篇关于重新选择时清除级联ComboBox的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!