我一直在互联网上寻找答案,但大多数人说使用数据验证并不能真正解决我的问题。我想做的是,假设我有ListBox1,它具有3个值(红色,蓝色,绿色),还有另一个列表框(ListBox2),我希望根据答案在工作表中显示列表的值第一个ListBox的。例如:我从listbox1中选择红色,然后希望listbox2中具有列表“ red”(苹果,可乐,火)中的选项。在此,我将不胜感激。谢谢
您可以使用以下方法(根据需要进行调整):
Private Sub ListBox1_Click()
With Me.ListBox2
.Clear
.List = Application.Transpose(GetColorItemsRange(Me.ListBox1.value)) 'fill referenced listbox with values from the range returned by GetColorItemsRange function
End With
End Sub
Function GetColorItemsRange(colorValue As String) As Range
With Worksheets("ColorItames") ' change "ColorItames" with actual name of your worksheet with items associated to colors
With .Range("A1", .Cells(1, .Columns.Count).End(xlToLeft)).Find(what:=colorValue, LookIn:=xlValues, lookat:=xlWhole) 'find and reference the referenced sheet row 1 cell matching the passed value
Set GetColorItemsRange = .Parent.Range(.Cells.Offset(1), .Cells.End(xlDown)) 'return the range ranging from referenced range down to last not empty cell before first empty cell
End With
End With
End Function
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句