在一張紙上，我有一個供應商串列及其詳細資訊，我有一個用戶，其中包含一個自動從供應商串列中填充的組合框。在供應商旁邊的列中，我有地址、電話號碼等詳細資訊。我想做的是在用戶進行選擇后，我希望代碼獲取相鄰列中的詳細資訊并填寫表格. 我曾嘗試使用查找功能，但是我經常收到錯誤訊息，指出找不到物件。以下是我到目前為止所擁有的
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Worksheets("RFQ Information")
'Take supplier name from combobox
'Copy row data in supplier sheet and paste (transposed) into form
Dim xRg As Range
Set xRg = Worksheets("Suppliers").Range("A2:H15")
Set Cells(53, 1) = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, xRg, 2, False)
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim SupplierName As Range
Dim SupSheet As Worksheet
Dim tbl As ListObject
Dim SupArray As Variant
Dim SupString As String
Set SupSheet = Sheets("Suppliers")
Set tbl = SupSheet.ListObjects("Table1")
Set SupplierName = tbl.ListColumns(1).DataBodyRange
SupArray = SupplierName.Value
ComboBox1.List = SupArray
UserForm1.Show
MsgBox ("done")
End Sub
uj5u.com熱心網友回復：
我建議使用 ComboBox Change 事件而不是按鈕，因為您需要有關串列選擇的資訊。您還可以利用 ComboBox.ListIndex 屬性來獲取所選專案在串列中的位置，然后使用它從資料表中獲取相鄰的值。以下是如何執行此操作的快速示例：
Private Sub ComboBox1_Change()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsSup As Worksheet: Set wsSup = wb.Worksheets("Suppliers")
Dim rData As Range: Set rData = wsSup.ListObjects("Table1").DataBodyRange
Dim i As Long: i = Me.ComboBox1.ListIndex 1
If i = 0 Then Exit Sub 'Nothing selected
'Second number is the column
' Column 1 is the Supplier
' Column 2 is the next column (phone maybe?)
' Column 3 is the column after that (address maybe?)
MsgBox rData.Cells(i, 2) & Chr(10) & _
rData.Cells(i, 3)
'Load the values you want into the necessary form controls
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/495991.html
上一篇：VBA回圈和做while