如何使用现有数组填充组合框 [英] How Do I Populate a ComboBox With an Existing Array

问题描述

当我尝试编辑现有项目的详细信息时，我的代码无法为列表中的最后两个项目填写相应的信息。有一个组合框用于选择要编辑的商品，然后有一个商品ID的文本框，以及一个商品订购日期和发货时间的文本框，还有两个组合框用于选择发货状态和在线商店它是通过购买的。这些字段均自动填充有所选项目的相应信息。除项目名称本身和项目ID以外，可以编辑与所选项目相对应的详细信息。它适用于除最后2个项目之外的所有项目，我不知道为什么。以下是用于编辑现有项目的代码：

When I try to edit details of an existing item, my code fails to fill in the corresponding information for the last two items in the list. There is a combobox for selecting the item to edit, then a textbox for the item ID, as well as a textbox for the dates the item was ordered and when it was shipped, then two more comboboxes for selecting the shipping status and the online store it was purchased through. These fields are all auto-filled with the corresponding information for the item that is selected. The details corresponding to the selected item can be edited with the exception of the item name itself and the item ID. It works for every item EXCEPT the last 2 items and I have no idea why. Here is the code for editing an existing item:

Option Explicit


Private Sub cboOrderedFrom2_Change()

    cboOrderedFrom2.BackColor = vbWhite
    lblOrderedFrom2.ForeColor = vbBlack

End Sub

Private Sub cboOrderStatus2_Change()

    cboOrderedFrom2.BackColor = vbWhite
    lblOrderStatus2.ForeColor = vbBlack

End Sub

Private Sub cboRemoveOrEditItemDetails_Change()

    cboRemoveOrEditItemDetails.BackColor = vbWhite
    lblItemDescription2.ForeColor = vbBlack

    Dim ws As Worksheet, i As Integer, wsLR As Variant

    Set ws = ThisWorkbook.Sheets("Sheet1")

    wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Rows

    For i = 3 To wsLR

        If ws.Cells(i, 2) = Me.cboRemoveOrEditItemDetails Then
            Me.txtItemID.Value = ws.Cells(i, "A")
            Me.txtPiecesIncluded2.Value = ws.Cells(i, "C")
            Me.txtOrderDate2.Value = ws.Cells(i, "E")
            Me.cboOrderStatus2.Value = ws.Cells(i, "G")
            Me.txtQuantityOrdered2.Value = ws.Cells(i, "D")
            Me.txtDateShipped2.Value = ws.Cells(i, "F")
            Me.cboOrderedFrom2.Value = ws.Cells(i, "H")
            Exit Sub
        End If
    Next i

End Sub

Private Sub cmdAddStore_Click()

    frmAddStore.Show

End Sub

Private Sub cmdCancelEditOrRemoveItemDetails_Click()

    Unload Me

End Sub

'Private Sub cmdRemoveItemDetails_Click()

    'Dim ws As Worksheet, i As Integer, wsLR As Variant

    'Set ws = ThisWorkbook.Sheets("Sheet1")

    'wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Rows

    'For i = 3 To wsLR

        'If ws.Cells(i, 2) = Me.cboRemoveOrEditItemDetails Then
            'Rows(i).EntireRow.Delete
            'Sheet1.Activate
            'Range("A1").End(xlDown).Offset(1, 0).Select
            'ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + 1
            'Unload Me
        'End If

    'Next i

'End Sub

Private Sub cmdSubmitEditItemDetails_Click()

    If txtPiecesIncluded2.BackColor = vbRed Then
        Exit Sub
    End If

    If txtQuantityOrdered2.BackColor = vbRed Then
        Exit Sub
    End If

    If cboOrderStatus2.BackColor = vbRed Then
        Exit Sub
    End If

    If cboOrderedFrom2.BackColor = vbRed Then
        Exit Sub
    End If

    If cboRemoveOrEditItemDetails.Value = "" Then
        cboRemoveOrEditItemDetails.BackColor = vbRed
        lblItemDescription2.ForeColor = vbRed
        Exit Sub
    End If

    If cboRemoveOrEditItemDetails.BackColor = vbRed Then
        Exit Sub
    End If

    Dim ws As Worksheet, i As Integer, wsLR As Variant

    Set ws = ThisWorkbook.Sheets("Sheet1")

    wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Rows

    For i = 3 To wsLR

        If ws.Cells(i, "B") = Me.cboRemoveOrEditItemDetails Then
            ws.Cells(i, "A") = Me.txtItemID.Value
            ws.Cells(i, "C") = Me.txtPiecesIncluded2.Value
            ws.Cells(i, "E") = Me.txtOrderDate2.Value
            ws.Cells(i, "G") = Me.cboOrderStatus2.Value
            ws.Cells(i, "D") = Me.txtQuantityOrdered2.Value
            ws.Cells(i, "F") = Me.txtDateShipped2.Value
            ws.Cells(i, "H") = Me.cboOrderedFrom2.Value
            Unload Me
            Exit Sub
        End If
    Next i

End Sub

Private Sub spnPiecesIncluded2_Change()

    txtPiecesIncluded2.Value = spnPiecesIncluded2.Value

End Sub

Private Sub spnQuantityOrdered2_Change()

    txtQuantityOrdered2.Value = spnQuantityOrdered2.Value

End Sub



Private Sub txtPiecesIncluded2_Change()

    If IsNumeric(txtPiecesIncluded2.Value) And txtPiecesIncluded2.Value >= spnPiecesIncluded2.Min And _
    txtPiecesIncluded2.Value <= spnPiecesIncluded2.Max Then
        spnPiecesIncluded2.Value = txtPiecesIncluded2.Value
        txtPiecesIncluded2.BackColor = vbWhite
        lblPiecesIncluded2.ForeColor = vbBlack
    Else
        txtPiecesIncluded2.BackColor = vbRed
        lblPiecesIncluded2.ForeColor = vbRed
    End If
End Sub

Private Sub txtQuantityOrdered2_Change()

    If IsNumeric(txtQuantityOrdered2.Value) And txtQuantityOrdered2.Value >= spnQuantityOrdered2.Min And _
    txtQuantityOrdered2.Value <= spnQuantityOrdered2.Max Then
        spnQuantityOrdered2.Value = txtQuantityOrdered2.Value
        txtQuantityOrdered2.BackColor = vbWhite
        lblQuantityOrdered2.ForeColor = vbBlack
    Else
        txtQuantityOrdered2.BackColor = vbRed
        lblQuantityOrdered2.ForeColor = vbRed
    End If
End Sub

Private Sub UserForm_Click()

End Sub

推荐答案

填充 ComboxBox 非常简单。 .List 属性可以接受数组，也可以使用 .AddItem 方法单独添加项目。

Populating a ComboxBox is pretty straight forward. The .List property can accept an array, or you can add items individually with the .AddItem method.

我从您的代码中注意到，您正在一次读取数组中的每个单元格。您是否知道可以一次完成所有操作？假设您定义所需范围的代码如下所示：

I notice from your code that you are reading each cell at a time in to your array. Are you aware that you can do it all in one go? Let's say your code to define the desired range looks something like this:

Dim lastRow As Long, lastCol As Long
Dim readRange As Range

'Define the range to be read
With Sheet1
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
    Set readRange = .Range(.Cells(3, "A"), .Cells(lastRow, lastCol))
End With

结尾

然后填充数组的代码可能只是一行：

Then the code to populate your array, could simply be one line:

Dim data As Variant

'Read range to array
data = readRange.Value2

将数组写入工作表：

Dim writeRange As Range

'Write the data
Set writeRange = Sheet2.Range("A1").Resize(UBound(data, 1), UBound(data, 2))
writeRange.Value = data

下面是三个例子用全部或部分数组对您的 ComboBox 进行食入：

Below are three examples of populating your ComboBox with all or part of the array:

'Populate the combobox
UserForm1.ComboBox1.List = data

'Or, if you want more than one column in combobox
With UserForm1.ComboBox2
    .ColumnCount = UBound(data, 2)
    .List = data
End With


'Or, if you want a specific index (not the first) from your array
Dim r As Long, index As Long
index = 2
For r = 1 To UBound(data, 1)
    UserForm1.ComboBox3.AddItem data(r, index)
Next

这篇关于如何使用现有数组填充组合框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！