User Form Search and Update Issues

%3CLINGO-SUB%20id%3D%22lingo-sub-2790407%22%20slang%3D%22en-US%22%3EUser%20Form%20Search%20and%20Update%20Issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2790407%22%20slang%3D%22en-US%22%3E%3CP%3EAttached%20is%20workbook%20I%20am%20using%20to%20track%20Inventory.%20Under%20the%20first%20tab%20I%20have%20a%20Tools%20%26amp%3B%20Equip.%20tab.%20There%20is%20a%20macro%20that%20will%20bring%20up%20a%20user%20form%20to%20search%20for%20an%20item%20within%20the%20data%20base.%20I%20could%20then%20update%20the%20data%20in%20the%20user%20form%20and%20use%20the%20update%20button%20within%20the%20form%20to%20update%20the%20row%20of%20data.%20I%20have%20the%20code%20as%20good%20as%20I%20think%20I%20can%20make%20it%20but%20I%20am%20getting%20no%20where.%20Please%20help.%3C%2FP%3E%3CP%3EThanks!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBelow%20is%20the%20code%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton1_Click()%3CBR%20%2F%3EDim%20PRODUCT_ID%20As%20String%3CBR%20%2F%3EPRODUCT_ID%20%3D%20Trim(TextBox1.Text)%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(Row.Count%2C%202).End(xlUp).Row%3C%2FP%3E%3CP%3EFor%20i%20%3D%202%20To%20lastrow%3CBR%20%2F%3EIf%20Worksheets(%22Sheet4%22).Cells(i%2C%201).Value%20%3D%20PRODUCT_ID%20Then%3CBR%20%2F%3ETextBox2.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%202).Value%3CBR%20%2F%3EComboBox1.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%203).Value%3CBR%20%2F%3EComboBox2.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%204).Value%3CBR%20%2F%3ETextBox5.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%205).Value%3CBR%20%2F%3ETextBox6.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%206).Value%3CBR%20%2F%3ETextBox7.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%207).Value%3CBR%20%2F%3ETextBox8.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%208).Value%3CBR%20%2F%3ETextBox9.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%209).Value%3CBR%20%2F%3ETextBox10.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%2010).Value%3CBR%20%2F%3ETextBox11.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%2011).Value%3CBR%20%2F%3ETextBox12.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%2012).Value%3CBR%20%2F%3ETextBox13.Text%20%3D%20Worksheets(%22Sheet4%22).Cells(i%2C%2013).Value%3C%2FP%3E%3CP%3EEnd%20If%3CBR%20%2F%3ENext%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3EPrivate%20Sub%20ToggleButton1_Click()%3CBR%20%2F%3EDim%20PRODUCT_ID%20As%20String%3CBR%20%2F%3EPRODUCT_ID%20%3D%20Trim(TextBox1.Text)%3CBR%20%2F%3Elastrow%20%3D%20Worksheets(%22Sheet4%22).Cells(Row.Count%2C%202).End(xlUp).Row%3C%2FP%3E%3CP%3EFor%20i%20%3D%202%20To%20lastrow%3C%2FP%3E%3CP%3EIf%20Worksheets(%22Sheet4%22).Cells(i%2C%201).Value%20%3D%20PRODUCT_ID%20Then%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%202).Value%20%3D%20TextBox2.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%203).Value%20%3D%20ComboBox1.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%204).Value%20%3D%20ComboBox2.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%205).Value%20%3D%20TextBox5.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%206).Value%20%3D%20TextBox6.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%207).Value%20%3D%20TextBox7.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%208).Value%20%3D%20TextBox8.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%209).Value%20%3D%20TextBox9.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%2010).Value%20%3D%20TextBox10.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%2011).Value%20%3D%20TextBox11.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%2012).Value%20%3D%20TextBox12.Text%3CBR%20%2F%3EWorksheets(%22Sheet4%22).Cells(i%2C%2013).Value%20%3D%20TextBox13.Text%3C%2FP%3E%3CP%3EEnd%20If%3CBR%20%2F%3ENext%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2790407%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2791441%22%20slang%3D%22en-US%22%3ERe%3A%20User%20Form%20Search%20and%20Update%20Issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2791441%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1154920%22%20target%3D%22_blank%22%3E%40jdavis1994%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20ID%20number%20to%20get%20past%20the%20user%20form%20is%201234%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2807625%22%20slang%3D%22en-US%22%3ERe%3A%20User%20Form%20Search%20and%20Update%20Issues%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2807625%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1154920%22%20target%3D%22_blank%22%3E%40jdavis1994%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIssue%20resolved%20thanks%20to%20a%20contributor%20of%20this%20forum.%20See%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Attached is workbook I am using to track Inventory. Under the first tab I have a Tools & Equip. tab. There is a macro that will bring up a user form to search for an item within the data base. I could then update the data in the user form and use the update button within the form to update the row of data. I have the code as good as I think I can make it but I am getting no where. Please help.

Thanks!

 

Below is the code:

 

Private Sub CommandButton1_Click()
Dim PRODUCT_ID As String
PRODUCT_ID = Trim(TextBox1.Text)
Worksheets("Sheet4").Cells(Row.Count, 2).End(xlUp).Row

For i = 2 To lastrow
If Worksheets("Sheet4").Cells(i, 1).Value = PRODUCT_ID Then
TextBox2.Text = Worksheets("Sheet4").Cells(i, 2).Value
ComboBox1.Text = Worksheets("Sheet4").Cells(i, 3).Value
ComboBox2.Text = Worksheets("Sheet4").Cells(i, 4).Value
TextBox5.Text = Worksheets("Sheet4").Cells(i, 5).Value
TextBox6.Text = Worksheets("Sheet4").Cells(i, 6).Value
TextBox7.Text = Worksheets("Sheet4").Cells(i, 7).Value
TextBox8.Text = Worksheets("Sheet4").Cells(i, 8).Value
TextBox9.Text = Worksheets("Sheet4").Cells(i, 9).Value
TextBox10.Text = Worksheets("Sheet4").Cells(i, 10).Value
TextBox11.Text = Worksheets("Sheet4").Cells(i, 11).Value
TextBox12.Text = Worksheets("Sheet4").Cells(i, 12).Value
TextBox13.Text = Worksheets("Sheet4").Cells(i, 13).Value

End If
Next

End Sub

Private Sub ToggleButton1_Click()
Dim PRODUCT_ID As String
PRODUCT_ID = Trim(TextBox1.Text)
lastrow = Worksheets("Sheet4").Cells(Row.Count, 2).End(xlUp).Row

For i = 2 To lastrow

If Worksheets("Sheet4").Cells(i, 1).Value = PRODUCT_ID Then
Worksheets("Sheet4").Cells(i, 2).Value = TextBox2.Text
Worksheets("Sheet4").Cells(i, 3).Value = ComboBox1.Text
Worksheets("Sheet4").Cells(i, 4).Value = ComboBox2.Text
Worksheets("Sheet4").Cells(i, 5).Value = TextBox5.Text
Worksheets("Sheet4").Cells(i, 6).Value = TextBox6.Text
Worksheets("Sheet4").Cells(i, 7).Value = TextBox7.Text
Worksheets("Sheet4").Cells(i, 8).Value = TextBox8.Text
Worksheets("Sheet4").Cells(i, 9).Value = TextBox9.Text
Worksheets("Sheet4").Cells(i, 10).Value = TextBox10.Text
Worksheets("Sheet4").Cells(i, 11).Value = TextBox11.Text
Worksheets("Sheet4").Cells(i, 12).Value = TextBox12.Text
Worksheets("Sheet4").Cells(i, 13).Value = TextBox13.Text

End If
Next

End Sub

2 Replies

@jdavis1994 

The ID number to get past the user form is 1234

@jdavis1994 

Issue resolved thanks to a contributor of this forum. See attached.