I HAVE THE ATTACHED THE CODE BELOW,
WHILE AMENDING THE EXISTING VALUES IN THE EXCEL SHEET USING LISTBOX FOR SLECTION TO SHOW THE VALUES IN THE TEXT BOX AND THEN UPDATING OR AMENDING VALUES.
MY CODE DOES THE AMENDMENT TO THE EXACT ROW BUT ALSO UPDATES THE SAME IN ROW 1. KINDLY HELP ?
Private Sub CMDUPDATE_Click()
Sheet2.Unprotect PASSWORD:="XXXX"
Dim x As Long
Dim y As Long
x = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheet2.Cells(y, 1).Value = Me.ListBox1.Column(0, 1) Then
Sheet2.Cells(y, 1).Value = TextBox1.Value
Sheet2.Cells(y, 2).Value = TextBox2.Value
Sheet2.Cells(y, 3).Value = TextBox3.Value
Sheet2.Cells(y, 4).Value = TextBox4.Value
Sheet2.Cells(y, 5).Value = TextBox5.Value
Sheet2.Cells(y, 6).Value = TextBox6.Value
Sheet2.Cells(y, 7).Value = TextBox7.Value
End If
Next y
Unload Me
CMDADD.Show
Sheet2.Protect PASSWORD:="XXXX"
ThisWorkbook.Save
End Sub
WHILE AMENDING THE EXISTING VALUES IN THE EXCEL SHEET USING LISTBOX FOR SLECTION TO SHOW THE VALUES IN THE TEXT BOX AND THEN UPDATING OR AMENDING VALUES.
MY CODE DOES THE AMENDMENT TO THE EXACT ROW BUT ALSO UPDATES THE SAME IN ROW 1. KINDLY HELP ?
Private Sub CMDUPDATE_Click()
Sheet2.Unprotect PASSWORD:="XXXX"
Dim x As Long
Dim y As Long
x = Sheet2.Range("A" & Rows.Count).End(xlUp).Row
For y = 2 To x
If Sheet2.Cells(y, 1).Value = Me.ListBox1.Column(0, 1) Then
Sheet2.Cells(y, 1).Value = TextBox1.Value
Sheet2.Cells(y, 2).Value = TextBox2.Value
Sheet2.Cells(y, 3).Value = TextBox3.Value
Sheet2.Cells(y, 4).Value = TextBox4.Value
Sheet2.Cells(y, 5).Value = TextBox5.Value
Sheet2.Cells(y, 6).Value = TextBox6.Value
Sheet2.Cells(y, 7).Value = TextBox7.Value
End If
Next y
Unload Me
CMDADD.Show
Sheet2.Protect PASSWORD:="XXXX"
ThisWorkbook.Save
End Sub