gpt4 book ai didi

Excel VBA 用户窗体使用复选框将数据输入多行

转载 作者:行者123 更新时间:2023-12-03 02:21:46 26 4
gpt4 key购买 nike

您好,我需要根据所选的复选框一次输入多行数据。目前这仅添加 1 行。我想我必须使用循环,但我不确定应该如何实现它。有人可以帮忙吗?

Userform

示例输出应如下所示:

TC37    | 1TC37    | 2TC37    | 4

Current Code:

Dim LastRow As Long, ws As Worksheet
Private Sub CommandButton1_Click()

Set ws = Sheets("sheet1")

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

ws.Range("A" & LastRow).Value = ComboBox1.Text

If CheckBox1.Value = True Then
ws.Range("B" & LastRow).Value = "1"
End If

If CheckBox2.Value = True Then
ws.Range("B" & LastRow).Value = "2"
End If

If CheckBox3.Value = True Then
ws.Range("B" & LastRow).Value = "3"
End If

If CheckBox4.Value = True Then
ws.Range("B" & LastRow).Value = "4"
End If

End Sub

Private Sub UserForm_Initialize()
ComboBox1.List = Array("TC37", "TC38", "TC39", "TC40")
End Sub

最佳答案

由于您一次获取最后一行,因此您应该引用该一次转储数据。尝试这样的事情:

Dim chkCnt As Integer
Dim ctl As MSForms.Control, i As Integer, lr As Long
Dim cb As MSForms.CheckBox

With Me
'/* check if something is checked */
chkCnt = .CheckBox1.Value + .CheckBox2.Value + .CheckBox3.Value + .CheckBox4.Value
chkCnt = Abs(chkCnt)
'/* check if something is checked and selected */
If chkCnt <> 0 And .ComboBox1 <> "" Then
ReDim mval(1 To chkCnt, 1 To 2)
i = 1
'/* dump values to array */
For Each ctl In .Controls
If TypeOf ctl Is MSForms.CheckBox Then
Set cb = ctl
If cb Then
mval(i, 1) = .ComboBox1.Value
mval(i, 2) = cb.Caption
i = i + 1
End If
End If
Next
End If
End With
'/* dump array to sheet */
With Sheets("Sheet1") 'Sheet1
lr = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A" & lr).Resize(UBound(mval, 1), 2) = mval
End With

关于Excel VBA 用户窗体使用复选框将数据输入多行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51778352/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com