☰

Excel VBA/用户窗体，如何告诉用户窗体我正在搜索的行并输入数据

tag-icon • tag-icon microsoft-excel vba
Excel VBA/用户窗体，如何告诉用户窗体我正在搜索的行并输入数据

感谢 Christofer Weber，我刚刚改进了我的代码并改变了我的问题：

该代码用于第一步搜索发酵罐编号，当找到该编号后，系统将要求用户在用户表单中输入数据，并将该信息放入与找到的发酵罐编号相对应的特定行中。

现在的问题是我不知道如何将这两个步骤联系起来。可以成功找到号码，之后会要求用户输入信息，我设置了一个可以在两个子程序中使用的公共变量。但不知何故，数据似乎仍然无法输入到电子表格中。

这是我的代码：

Public m as Long Sub CmdSearch3_Click()

Dim FerNum As String, totRows As Long, ws As Worksheet

Set ws = Worksheets("Sheet1")
totRows = ws.Range("A1").CurrentRegion.Rows.Count
FerNum = CStr(InputBox("Please enter the fermenter number you are looking for."))

If FerNum = "" Then
    MsgBox "Enter the fermenter number you wish to search."

Else

    For m = 2 To totRows

        If Trim(Sheet1.Cells(m, 3)) <> Trim(FerNum) And m = totRows Then
            MsgBox ("Sorry, the fermenter number " & FerNum & " is not found.")

        ElseIf Trim(Sheet1.Cells(m, 3)) = Trim(FerNum) Then
            MsgBox ("Please enter the data need here.")
            DTPickerActualHarvestDate.SetFocus
            Exit For
        End If
    Next m

End If

End Sub

这是输入数据的第二部分。

Sub cmdAddRecord_Click()

Dim wks As Worksheet, AddNew As Range
Set wks = sheet1
If m>0 Then
m=m-1
End if
Set AddNew = wks.Range("A1")
AddNew.Offset(m, 6).Value = DTPickerActualHarvestDate.Value
AddNew.Offset(m, 7).Value = txtpH.Text
AddNew.Offset(m, 8).Value = cboNumberofCases.Value

AddNew.Offset(m, 10).Value = cboNumberofPails2gal.Text

AddNew.Offset(m, 12).Value = cboNumberofPails5gal.Text

AddNew.Offset(m, 13).Value = txtRetailPouchWeight1.Text
AddNew.Offset(m, 14).Value = txtRetailPouchWeight2.Text
AddNew.Offset(m, 15).Value = txtRetailPouchWeight3.Text
AddNew.Offset(m, 16).Value = txt2galPailsWeight1.Text
AddNew.Offset(m, 17).Value = txt2galPailsWeight2.Text
AddNew.Offset(m, 18).Value = txt2galPailsWeight3.Text
AddNew.Offset(m, 19).Value = txt5galPailsWeight1.Text
AddNew.Offset(m, 20).Value = txt5galPailsWeight2.Text
AddNew.Offset(m, 21).Value = txt5galPailsWeight3.Text

End Sub

答案1

从你帖子的标题来看，问题在于如何告诉用户表单你正在搜索哪一行。但在描述中，你问“我应该设置一个点击按钮或其他东西，以便可以在电子表格中输入数据吗？”我会回答后者：“如果你愿意，你可以随心所欲地做。”

至于第一部分，您要么必须传递变量，要么 - 在这种情况下似乎更容易 - 使其成为全局变量。

Public i as Long

Sub CmdSearch3_Click()

Dim totRows As Long, FerNum As String, ws As Worksheet
Set ws = Worksheets("Sheet1")
totRows = ws.Range("A1").CurrentRegion.Rows.Count
FerNum = CStr(InputBox("Please enter the fermenter number you are looking for."))

If FerNum = "" Then
    MsgBox "Enter the fermenter number you wish to search."
Else
    For i = 2 To totRows
        If Trim(ws.Cells(i, 3)) <> Trim(FerNum) And i = totRows Then
            MsgBox ("Sorry, the fermenter number " & FerNum & " is not found.")
        ElseIf Trim(ws.Cells(i, 3)) = Trim(FerNum) Then
            MsgBox ("Please enter the data need here.")
            DTPickerActualHarvestDate.SetFocus
            Exit For
        End If
    Next i
End If
End Sub

用这个来替换你的顶部代码怎么样，然后我猜你会cmdAddRecord_Click()通过按下按钮来让用户窗体调用。

或者，您可以用普通的输入框替换用户表单输入框。

声明另一个公共

Public DTPickerActualHarvestDate As String

然后将找到匹配的代码更改为：

    ElseIf Trim(ws.Cells(i, 3)) = Trim(FerNum) Then
        DTPickerActualHarvestDate = CStr(InputBox("Please enter the data need here."))
        cmdAddRecord_Click
        Exit For
    End If

然后AddNew.Offset(i, 6).Value = DTPickerActualHarvestDate.Value删除.value

编辑

这是我尝试过的，似乎效果不错。不过我不得不注释掉 Userform 的内容，因为我没有这个。

Public m As Long
Public ws As Worksheet
Sub CmdSearch3_Click()

Dim FerNum As String, totRows As Long
Set ws = Worksheets("Sheet1")
totRows = ws.Range("A1").CurrentRegion.Rows.Count
FerNum = CStr(InputBox("Please enter the fermenter number you are looking for."))

If FerNum = "" Then
    MsgBox "Enter the fermenter number you wish to search."
Else
    For m = 2 To totRows

        If Trim(ws.Cells(m, 3)) <> Trim(FerNum) And m = totRows Then
            MsgBox ("Sorry, the fermenter number " & FerNum & " is not found.")

        ElseIf Trim(ws.Cells(m, 3)) = Trim(FerNum) Then
            MsgBox ("Please enter the data need here.")
            DTPickerActualHarvestDate.SetFocus
            Exit For
        End If
    Next m

End If

End Sub

Sub cmdAddRecord_Click()
Dim AddNew As Range
Set AddNew = ws.Range("A1")
If m > 0 Then
    m = m - 1
    AddNew.Offset(m, 6).Value = DTPickerActualHarvestDate.Value
    AddNew.Offset(m, 7).Value = txtpH.Text
    AddNew.Offset(m, 8).Value = cboNumberofCases.Value
    AddNew.Offset(m, 10).Value = cboNumberofPails2gal.Text
    AddNew.Offset(m, 12).Value = cboNumberofPails5gal.Text
    AddNew.Offset(m, 13).Value = txtRetailPouchWeight1.Text
    AddNew.Offset(m, 14).Value = txtRetailPouchWeight2.Text
    AddNew.Offset(m, 15).Value = txtRetailPouchWeight3.Text
    AddNew.Offset(m, 16).Value = txt2galPailsWeight1.Text
    AddNew.Offset(m, 17).Value = txt2galPailsWeight2.Text
    AddNew.Offset(m, 18).Value = txt2galPailsWeight3.Text
    AddNew.Offset(m, 19).Value = txt5galPailsWeight1.Text
    AddNew.Offset(m, 20).Value = txt5galPailsWeight2.Text
    AddNew.Offset(m, 21).Value = txt5galPailsWeight3.Text
Else
    MsgBox "Variable not set"
End If
End Sub

相关内容