你想知道如何从 Excel 工作表中检索数据并在用户表单中更新它。
在图片上您可以看到用户表单的样子。
我想做的是创建另一个用户表单,可以在工作表中搜索特定引用并更新该特定行的一些单元格。
This is the code I have now to insert data into the sheet.
Private Sub cmdClear_Click()
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub
Private Sub cmdSend_Click()
Dim RowCount As Long
Dim ctl As Control
' Check user input
If Me.combTechnieker.Value = "" Then
MsgBox "Dag vreemdeling! Welke van de 4 Mongolen ben je?", vbExclamation, "RMA invoer"
Me.combTechnieker.SetFocus
Exit Sub
End If
If Me.txtPcwRef.Value = "" Then
MsgBox "Vul onze referentie in!", vbExclamation, "RMA invoer"
Me.txtPcwRef.SetFocus
Exit Sub
End If
If Me.txtKlant.Value = "" Then
MsgBox "Vul de naam van de klant in!", vbExclamation, "RMA invoer"
Me.txtKlant.SetFocus
Exit Sub
End If
If Me.txtMerk.Value = "" Then
MsgBox "Vul het merk in!", vbExclamation, "RMA invoer"
Me.txtMerk.SetFocus
Exit Sub
End If
If Me.txtMerkRef.Value = "" Then
MsgBox "Vul de referentie van de fabrikant in!", vbExclamation, "RMA invoer"
Me.txtMerkRef.SetFocus
Exit Sub
End If
If Me.txtProduct.Value = "" Then
MsgBox "Vul het product in!", vbExclamation, "RMA invoer"
Me.txtProduct.SetFocus
Exit Sub
End If
If Me.txtSerienummer.Value = "" Then
MsgBox "Vul het serienummer in!", vbExclamation, "RMA invoer"
Me.txtSerienummer.SetFocus
Exit Sub
End If
If Me.txtProbleem.Value = "" Then
MsgBox "Vul de probleem omschrijving in!", vbExclamation, "RMA invoer"
Me.txtProbleem.SetFocus
Exit Sub
End If
If Me.txtOnderdelen.Value = "" Then
MsgBox "Bent u zeker dat er geen onderdelen achterblijven. Indien ja. Vul N/A in", vbExclamation, "RMA invoer"
Me.txtOnderdelen.SetFocus
Exit Sub
End If
' Write data to worksheet
RowCount = Worksheets("RMA 2016").Range("A1").CurrentRegion.Rows.Count
With Worksheets("RMA 2016").Range("A1")
.Offset(RowCount, 0).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
.Offset(RowCount, 1).Value = "Open"
.Offset(RowCount, 3).Value = Me.txtPcwRef.Value
.Offset(RowCount, 4).Value = Me.txtKlant.Value
.Offset(RowCount, 5).Value = Me.txtMerk.Value
.Offset(RowCount, 6).Value = Me.txtMerkRef.Value
.Offset(RowCount, 7).Value = Me.txtProduct.Value
.Offset(RowCount, 8).Value = Me.txtSerienummer.Value
.Offset(RowCount, 9).Value = Me.txtOnderdelen.Value
.Offset(RowCount, 10).Value = Me.txtProbleem.Value
.Offset(RowCount, 13).Value = Me.combTechnieker.Value
If Me.chkGarantie.Value = True Then
.Offset(RowCount, 2).Value = "Ja"
Else
.Offset(RowCount, 2).Value = "Nee"
End If
End With
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub
Private Sub UserForm_Click()
End Sub