我創建了一個帶有一些組合框的用戶表單，基于組合框2，我想填充組合框1。
在combobox2中有6個專案可供選擇，17、19、21、23、25、25
根據 Combobox1 中的選定專案，我想填充 combobox2 如下：
如果選擇了 ItemA，則應從作業表“supply_to_production”范圍 (T6:T1000) 中的范圍填充 Combobox1
如果選擇了 ItemB，則應從作業表“supply_to_production”范圍 (V6:V1000) 中的范圍填充 Combobox1
如果選擇了 ItemC，則應從作業表“supply_to_production”范圍 (X6:X1000) 中的范圍填充 Combobox1
如果選擇 ItemD，則應從作業表“supply_to_production”范圍 (Z6:Z1000) 中的范圍填充 Combobox1
如果選擇了 ItemE，則應從作業表“supply_to_production”范圍 (AB6:AB1000) 中的范圍填充 Combobox1
如果選擇了 ItemF，則應從作業表“supply_to_production”范圍 (AD 6:AD1000) 中的范圍填充 Combobox1
我試過下面的代碼，它沒有給我任何錯誤，但也沒有給我combobox1中的任何串列。
`
Private Sub UserForm_Initialize()
With ComboBox2
.AddItem "17"
.AddItem "19"
.AddItem "21"
.AddItem "23"
.AddItem "25"
.AddItem "25 "
End With
End Sub
Private Sub ComboBox1_Update()
Dim index As Integer
index = ComboBox2.ListIndex
ComboBox1.Clear
Select Case index
Case "17":
ComboBox1.List = [SUPPLY_TO_PRODUCTION!T6:T1000]
Case "19":
ComboBox1.List = [SUPPLY_TO_PRODUCTION!V6:V1000]
Case "21":
ComboBox1.List = [SUPPLY_TO_PRODUCTION!X6:X1000]
Case "23":
ComboBox1.List = [SUPPLY_TO_PRODUCTION!Z6:Z1000]
Case "25":
ComboBox1.List = [SUPPLY_TO_PRODUCTION!AB6:AB1000]
Case "25 ":
ComboBox1.List = [SUPPLY_TO_PRODUCTION!AD6:AD1000]
End Select
End Sub
`
uj5u.com熱心網友回復：
像這樣的東西應該適合你：
Private Sub UserForm_Initialize()
Me.ComboBox2.List = Array(17, 19, 21, 23, 25, "25 ")
End Sub
Private Sub ComboBox2_Change()
'Don't have magic numbers, have an easy way to change hard-coded values
Const lStartRow As Long = 6
Me.ComboBox1.Clear
Dim sColumn As String
Select Case CStr(Me.ComboBox2.Value)
Case "17": sColumn = "T"
Case "19": sColumn = "V"
Case "21": sColumn = "X"
Case "23": sColumn = "Z"
Case "25": sColumn = "AB"
Case "25 ": sColumn = "AD"
End Select
If Len(sColumn) = 0 Then Exit Sub 'No valid option selected from ComboBox2
'Dynamically size the data
Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("SUPPLY_TO_PRODUCTION")
Dim rData As Range: Set rData = ws.Range(ws.Cells(lStartRow, sColumn), ws.Cells(ws.Rows.Count, sColumn).End(xlUp))
Me.ComboBox1.List = rData.Value
End Sub
uj5u.com熱心網友回復：
看起來你正在編譯，哪里Option Explicit可能救了你？
嘗試參考完整范圍并使用ComboBox2.Valuefor your Select，例如：
Private Sub ComboBox1_Change()
Dim columnReference as String
Select Case ComboBox2.Value
Case "17":
columnReference = "T"
Case "19":
columnReference = "V"
Case "21":
columnReference = "X"
Case "23":
columnReference = "Z"
Case "25":
columnReference = "AB"
Case "25 ":
columnReference = "AD"
End Select
With Sheets("SUPPLY_TO_PRODUCTION")
ComboBox2.List = .Range(.Cells(6,columnReference),.Cells(1000,columnReference)).Value
End With
End Sub
Edit1：添加了子程式的名稱... Private Sub ComboBox1_Change()，它應該觸發事件。另外，顯示遵循此模型的快速示例的影像：
Option Explicit
Private Sub UserForm_Initialize()
ComboBox1.List = Array("animal", "food")
End Sub
Private Sub combobox1_change()
Dim columnReference As Long
Select Case ComboBox1.Value
Case "animal"
columnReference = 1
Case "food"
columnReference = 2
End Select
With Sheets(1)
ComboBox2.List = .Range(.Cells(2, columnReference), .Cells(4, columnReference)).Value
End With
End Sub
uj5u.com熱心網友回復：
根據另一個組合框中的選擇填充組合框
UserForm 例如UserForm1（小心）
Private Sub UserForm_Initialize()
ComboBox2.List = Array("17", "19", "21", "23", "25", "25 ")
End Sub
Private Sub ComboBox2_Change()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets("SUPPLY_TO_PRODUCTION")
Dim arg As Range: Set arg = ws.Range("T6:T1000," _
& "V6:V1000,X6:X1000,Z6:Z1000,AB6:AB1000,AD6:AD1000")
Dim aIndex As Long: aIndex = ComboBox2.ListIndex 1 ' zero-based
Dim Data() As Variant: Data = GetColumnRange(arg.Areas(aIndex))
Dim dict As Object: Set dict = DictColumn(Data)
If dict Is Nothing Then Exit Sub
ComboBox1.List = dict.Keys
End Sub
標準模塊 例如Module1
Function GetColumnRange( _
ByVal rg As Range, _
Optional ByVal ColumnNumber As Long = 1) _
As Variant
If rg Is Nothing Then Exit Function
If ColumnNumber < 1 Then Exit Function
If ColumnNumber > rg.Columns.Count Then Exit Function
With rg.Columns(ColumnNumber)
If rg.Rows.Count = 1 Then
Dim Data As Variant: ReDim Data(1 To 1, 1 To 1): Data(1, 1) = .Value
GetColumnRange = Data
Else
GetColumnRange = .Value
End If
End With
End Function
Function DictColumn( _
ByVal Data As Variant, _
Optional ByVal ColumnIndex As Variant) _
As Object
Const ProcName As String = "DictColumn"
On Error GoTo ClearError
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
dict.CompareMode = vbTextCompare ' case-insensitive
Dim c As Long
If IsMissing(ColumnIndex) Then
c = LBound(Data, 2) ' use first column Index2
Else
c = CLng(ColumnIndex)
End If
Dim Key As Variant
Dim r As Long
For r = LBound(Data, 1) To UBound(Data, 1)
Key = Data(r, c)
If Not IsError(Key) Then ' exclude error values
If Len(CStr(Key)) > 0 Then ' exclude blanks
dict(Key) = Empty
End If
End If
Next r
If dict.Count = 0 Then Exit Function ' only error values and blanks
Set DictColumn = dict
ProcExit:
Exit Function
ClearError:
Debug.Print "'" & ProcName & "' Run-time error '" _
& Err.Number & "':" & vbLf & " " & Err.Description
Resume ProcExit
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/526960.html