如何用您输入的产品名称填写用户表单 [英] How to fill out the userform by the product name you typed

问题描述

我希望我解释清楚

我有一个表格，可以在条目中键入并插入到工作表中，并且效果很好

I have a form that I type in the entries and insert into a sheet, and it works well

现在我需要在输入产品时，其余的框将从表中获取它们的值(如果存在的话)

Now I need that when I type a product, the rest of the boxes will get their values from the table, if they exist

也就是说，如果该产品已经存在，将填充相应的值

That is, if the product already exists, will fill the appropriate values

然后我根据需要进行更改，然后单击更新"按钮，并更新同一行

Then I change as needed, and click the Update button, and update the same line

所以我需要两件事

1. 如果该产品已经存在，请在表格的其余框中填写适当的值

1. If the product already exists, fill in the appropriate values in the rest of the boxes on the form

更新同一行产品

那是我的代码

    Private Sub update_Click()
    'When you click the Add button
    'Populates the data in the sheet
    '~~~~>>>>>> I do not know how to put on the same line of the selected product
        Dim lRow As Long
        Dim ws As Worksheet
        Set ws = Worksheets("sheet")
        lRow = ws.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Row
        With ws
            'Me.ComboBox3.Value >>>> Here he should look up the row with the same cell value and write down the following values
            .Cells(lRow, 2).Value = Me.TextBox2.Value
            .Cells(lRow, 2).Value = Me.TextBox1.Value
        End With
    End Sub
    Private Sub add_Click()
'Not related to here
    End Sub

我很乐意从这里的专家那里获得帮助/概念/指导

I would be happy to get help / concepts / direction from the experts here

注意:每种产品都有一个唯一的行

Note: There is a unique line for each product

如果不清楚，请给我写信

If this is not clear, please write to me

推荐答案

如果找到匹配的产品ID，您可以尝试自动填充其他2个字段.每次更改ComboBox1时，都会使用Range.Find方法查找产品ID.如果找到产品，它将使用Offset(r, c)

You can try this to auto populate your other 2 fields if matched product ID is found. Every time the ComboBox1 is changed, it will look for the Product ID using Range.Find method. If the product is found, it will then import the corresponding value from Column B and Column C by using Offset(r, c)

您将需要修改Range.Find方法的选项，以满足您的需求.有很多选项，因此最好查看此链接，并根据需要添加它们.听起来好像您需要Lookin:= xlWhole作为入门者.

You will need to ammend the options of Range.Find method to meet your needs. There are a lot of options, so it is best you see this link and add them as you see fit. Sounds like you will want Lookin:= xlWhole for starters.

Option Explicit

Private Sub ComboBox1_Change()

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("sheet")
Dim Found As Range

Set Found = ws.Range("A:A").Find(ComboBox1.Value)

If Not Found Is Nothing Then
    Me.TextBox1 = Found.Offset(, 1)
    Me.TextBox2 = Found.Offset(, 2)
Else                                            'Revert back to blank if nothing is found
    Me.TextBox1 = ""
    Me.TextBox2 = ""
End If

End Sub

这篇关于如何用您输入的产品名称填写用户表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！