我正在尝试在Excel中创建一个输入表单,该表单根据多个文本框设置行中单元格的值:
我设法磨碎了一些有用的东西,但是看起来笨拙。我最终可能得到n行(可能数百行)。编辑或排除故障会很痛苦。
我不知道如何将每个文本框引用到每个新行中的正确单元格。
如何将这段代码从n行减少到固定数目?
Private Sub CommandButton1_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")
Dim n As Long
Dim LastRow As Long
n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
sh.Range("A" & n + 1).Value = Me.TextBox1.Value
sh.Range("B" & n + 1).Value = Me.TextBox2.Value
sh.Range("C" & n + 1).Value = Me.TextBox3.Value
sh.Range("D" & n + 1).Value = Me.TextBox4.Value
sh.Range("E" & n + 1).Value = Me.TextBox5.Value
sh.Range("F" & n + 1).Value = Me.TextBox7.Value
sh.Range("G" & n + 1).Value = Me.TextBox8.Value
sh.Range("H" & n + 1).Value = Me.TextBox9.Value
sh.Range("I" & n + 1).Value = Me.TextBox10.Value
sh.Range("J" & n + 1).Value = Me.TextBox11.Value
End Sub
也许遵循预定义数组循环的方式:
Private Sub CommandButton1_Click()
Dim sh As Worksheet: Set sh = ThisWorkbook.Sheets("Sheet1")
Dim arr As Variant: arr = Evaluate("=""TextBox""&ROW(1:11)")
Dim lr As Long
With sh
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each el In arr
.Cells(lr + 1, Replace(el, "TextBox", "") * 1) = Me.Controls(el).Value
Next
End With
End Sub
您还可以决定遍历所有对象UserForm.Controls
并检查其TypeName
属性:
Private Sub CommandButton1_Click()
Dim sh As Worksheet: Set sh = ThisWorkbook.Sheets("Sheet1")
Dim ctrl As Control, lr As Long
With sh
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each ctrl In Me.Controls
If TypeName(ctrl) = "TextBox" Then
.Cells(lr + 1, Replace(ctrl.Name, "TextBox", "") * 1) = ctrl.Value
End If
Next
End With
End Sub
但是在这个例子中,您将遍历所有控件,并且可能不需要。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句