excel - Excel VBA Userform Entering Data into Multiple rows using checkboxes
问题描述
Hi I need to enter multiple rows of data at once based on the checkboxes that are selected. Currently this only adds 1 row. I think I have to use a loop but I'm not sure how I should implement it. Can anyone help please ?
The sample output should look something like this:
TC37 | 1 TC37 | 2 TC37 | 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
解决方案
由于您获得了最后一行 1 次,因此您应该参考那一次转储数据。尝试类似:
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
推荐阅读
- css - 如何使用 css hoverbox 制作过渡动画?
- ipfs - 是否需要集群来固定 ipfs 专用网络中的文件?
- android - 如何向 AOSP 添加自定义 INTENT 以授予对系统级控制调用的访问权限?
- drools - 如何在 OWL 本体上运行 Drools 规则引擎(KIE)?
- odata - ADF - Odata - 如何执行可选选择
- kotlin - Kotlin:多个命名的伴随对象
- python - 将具有相同大小和索引的数据帧相乘
- python - 如何删除字符串中的字母
- gnu-make - 如何根据条件的输出执行不同的配方?
- angular - Angular8 PrimeNG 反应形式的下拉菜单 - 初始值