UserForm VBA Coding - Update command button

%3CLINGO-SUB%20id%3D%22lingo-sub-2889445%22%20slang%3D%22en-US%22%3EUserForm%20VBA%20Coding%20-%20Update%20command%20button%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2889445%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20wondering%20if%20someone%20may%20be%20able%20to%20help%20me%20to%20restrict%20the%20following%20code%20to%20only%20updating%20one%20record%20within%20a%20Excel%20UserForm.%26nbsp%3B%20At%20present%20I%20have%20a%20search%20function%20which%20finds%20a%20name%20within%20textbox2%20for%20eg.%20Bill.%26nbsp%3B%20I%20alter%20the%20data%20that%20is%20populated%20in%20the%20form%20as%20required%20however%20when%20I%20select%20Update%20it%20updates%20all%20the%20%22Bill's%22%20within%20the%20spreadsheet%20rather%20than%20just%20the%20one%20that%20is%20loaded%20into%20the%20form.%3C%2FP%3E%3CP%3EAny%20advice%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20code%20is%20as%20follows%3A%3C%2FP%3E%3CP%3EPrivate%20Sub%20CommandButton6_Click()%3CBR%20%2F%3EDim%20Name%20As%20String%3CBR%20%2F%3EName%20%3D%20Trim(TextBox2.Text)%3CBR%20%2F%3Elastrow%20%3D%20Worksheets(%22Sheet1%22).Cells(Rows.Count%2C%201).End(xlUp).Row%3CBR%20%2F%3EFor%20i%20%3D%202%20To%20lastrow%3CBR%20%2F%3EIf%20Worksheets(%22Sheet1%22).Cells(i%2C%202).Value%20%3D%20TextBox2%20Then%3CBR%20%2F%3EWorksheets(%22Sheet1%22).Cells(i%2C%203)%20%3D%20TextBox3.Text%26nbsp%3B%3CBR%20%2F%3EWorksheets(%22Sheet1%22).Cells(i%2C%204)%20%3D%20TextBox4.Text%26nbsp%3B%3CBR%20%2F%3EWorksheets(%22Sheet1%22).Cells(i%2C%205)%20%3D%20TextBox5.Text%26nbsp%3B%3CBR%20%2F%3EWorksheets(%22Sheet1%22).Cells(i%2C%206)%20%3D%20TextBox6.Text%26nbsp%3B%3CBR%20%2F%3EWorksheets(%22Sheet1%22).Cells(i%2C%207)%20%3D%20TextBox7.Text%26nbsp%3B%3CBR%20%2F%3EWorksheets(%22Sheet1%22).Cells(i%2C%20%3CLI-EMOJI%20id%3D%22lia_smiling-face-with-sunglasses%22%20title%3D%22%3Asmiling_face_with_sunglasses%3A%22%3E%3C%2FLI-EMOJI%3E%20%3D%20Recommendations.Value%26nbsp%3B%3C%2FP%3E%3CP%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2889445%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

Hello 

I was wondering if someone may be able to help me to restrict the following code to only updating one record within a Excel UserForm.  At present I have a search function which finds a name within textbox2 for eg. Bill.  I alter the data that is populated in the form as required however when I select Update it updates all the "Bill's" within the spreadsheet rather than just the one that is loaded into the form.

Any advice would be greatly appreciated.

 

My code is as follows:

Private Sub CommandButton6_Click()
Dim Name As String
Name = Trim(TextBox2.Text)
lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Worksheets("Sheet1").Cells(i, 2).Value = TextBox2 Then
Worksheets("Sheet1").Cells(i, 3) = TextBox3.Text 
Worksheets("Sheet1").Cells(i, 4) = TextBox4.Text 
Worksheets("Sheet1").Cells(i, 5) = TextBox5.Text 
Worksheets("Sheet1").Cells(i, 6) = TextBox6.Text 
Worksheets("Sheet1").Cells(i, 7) = TextBox7.Text 
Worksheets("Sheet1").Cells(i, = Recommendations.Value 

End If
Next
End Sub

 

 

1 Reply

@Nicisherre 

How can we determine which row (record) is currently loaded?