如何在电子表格中正确的列中传输用户表单文本框的值

Question

我正在尝试创建一个用户表单，操作符将扫描他们的"操作"("OperationBox"文本框)，然后是他们的"员工ID #"("IDbox"文本框)将在电子表格的正确列中输入。输入的值也将显示在"列表框"中。表的名称是"Database";用户表单的名称是"scanform"。

我不知道如何做这个检查，然后用ID#填充电子表格单元格。任何帮助都太好了。

谢谢ScanForm数据库表格

Sub Submit()
'Transfer data from the form to the database
Dim sh As Worksheet
Dim iRow As Long
Set sh = ThisWorkbook.Sheets("Database")
'Utilizing iRow variable to store the next blank row number to transfer the data from Form to Database.
iRow = [Counta(Database!A:A) + 1]

With sh

.Cells(iRow, 1) = ScanForm.JobBox.Value

If .Cells(b, 1) = OperationBox.Value Then
.Cells(iRow, 2) = ScanForm.IDbox.Value
End If

If OperationBox.Value = .Cells("C1").Value Then
.Cells(iRow, 3) = ScanForm.IDbox.Value
End If

If OperationBox.Value = .Cells("D1").Value Then
.Cells(iRow, 4) = ScanForm.IDbox.Value
End If

.Cells(iRow, 5) = ScanForm.PartBox.Value
.Cells(iRow, 6) = ScanForm.QtyBox.Value
.Cells(iRow, 7) = [Text(Now(), "DD-MM-YY HH:MM")]
End With

结束子

Answer 1

循环在列标题与OperationBox。

Option Explicit
Sub Submit1()
'Transfer data from the form to the database
Dim sh As Worksheet, iRow As Long, c As Long
Dim rng As Range
Set sh = ThisWorkbook.Sheets("Database")

'Utilizing iRow variable to store the next blank row number
'to transfer the data from Form to Database.
iRow = sh.Cells(Rows.Count, 1).End(xlUp).Row + 1
' extend list box
Set rng = Range(Scanform.ListBox1.RowSource)
Scanform.ListBox1.RowSource = rng.Resize(iRow).Address

With sh
.Cells(iRow, 1) = Scanform.JobBox.Value

For c = 2 To 4
If .Cells(1, c) = Scanform.OperationBox.Value Then
.Cells(iRow, c) = Scanform.IDbox.Value
Exit For
End If
Next

.Cells(iRow, 5) = Scanform.PartBox.Value
.Cells(iRow, 6) = Scanform.QtyBox.Value
.Cells(iRow, 7) = [Text(Now(), "DD-MM-YY HH:MM")]
End With

End Sub