arrays - 检索组合框选择并写入单元格

Question

以下代码总结了我需要做的事情，其中​​我在用户窗体中有四个组合框：options1、options2、nights1 和 nights2。选项包含字符串信息，而夜晚包含数字。

Private Sub UserForm_Initialize()
    Dim i As Integer
    For i = 1 To 2
        Me.Controls("options" & CStr(i)).AddItem "Option 1"
        Me.Controls("options" & CStr(i)).AddItem "Option 2"
        Me.Controls("nights" & CStr(i)).AddItem "1"
        Me.Controls("nights" & CStr(i)).AddItem "2"

     Next i
End Sub

Private Sub btnEnd_Click()
    Dim options(1 To 2) As String
    Dim nights(1 To 2) As Integer

    For i = 1 To 2
        ThisWorkbook.Sheets("Sheet1").Cells(2, i + 1) = options(i)
        ThisWorkbook.Sheets("Sheet1").Cells(3, i + 1) = nights(i)

    Next i
End Sub

我需要将组合框中的选定选项写入指定的单元格，得到各种不同的结果，当前的结果是选项单元格中的空白值和夜间单元格中的 0。我对 VBA 还很陌生，所以非常感谢您的帮助！:)

标签： arraysexcelvbafor-loopcombobox

Answer 1

这是您可以填充组合框的方式：

    Private Sub UserForm_Initialize()

    '~~~> Add items to the combobox.
    cb1.List = Array("red", "blue")
    cb2.List = Array(1, 2)

    End Sub

** 注意，您将第二个组合框的值列为整数，因此不要将数字括在引号中。

这就是您可以检索组合框值并将它们写入单元格的方式。

Private Sub cmdBtn_Click()


'~~~> Declare the data types for the variables.
Dim wb As Workbook
Dim ws As Worksheet
Dim strSheet As String
Dim lrow_cb1 As Long
Dim lrow_cb2 As Long
Dim lcol As Long
Dim rng_cb1 As Range
Dim rng_cb2 As Range

'~~~> Assign objects/values to the variables.
strSheet = "Sheet1"
lrow_cb1 = 2
lrow_cb2 = 3
lcol = 0
Set wb = ThisWorkbook
Set ws = wb.Worksheets(strSheet)


'~~~> When the command button is clicked, write the cb1 and cb2 values to.....

'~~~> cb1 contains strings and cb2 contains integers
'~~~> cb1 to A2; cb2 to A3

'~~~> Start the column index.
lcol = lcol + 1

'~~~> Set the range variables.
Set rng_cb1 = ws.Cells(lrow_cb1, lcol)
Set rng_cb2 = ws.Cells(lrow_cb2, lcol)


'~~~> Assign the combobox selections to cells.
rng_cb1.Value = Me.cb1.Text
rng_cb2.Value = Me.cb2.Value


'~~~> Release the variables.
Set wb = Nothing
Set ws = Nothing
strWS = vbNullString
lrow_cb1 = vbNull
lrow_cb2 = vbNull
lcol = vbNull
Set rng_cb1 = Nothing
Set rng_cb2 = Nothing

End Sub

通过从工作表中单击按钮调用用户表单。

Private Sub cb_ShowUF_Click()

Call uf_ComboBox.Show

End Sub