make changes to a Row

[LegenDSlayeR]

Good Morning.
im hoping this is my problem, i need help with being able to edit a row. Userform1 has 2 listboxes. one show data from my worksheet "airstock" and the other listbox is a search. i have a search button which works fine searching for the data i need. however when i double click on the product i want to edit, it opens up a new userform to make the changes i need i only need to change the Quantity and Air location. . when i click on the update changes. it only changes in the listbox and not on the work sheet. can this be done ??

please see attached the userforms. and the codes


Userform 1 code for lstsearch

Private Sub lstsearch_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm2.TextBox2.Text = Me.lstsearch.Column(5)
    UserForm2.TextBox4.Text = Me.lstsearch.Column(1)
    UserForm2.TextBox3.Text = Me.lstsearch.Column(6)
    UserForm2.TextBox5.Text = Me.lstsearch.Column(0)
    UserForm2.TextBox6.Text = Me.lstsearch.Column(3)
    UserForm2.Show
    
    UserForm3.TextBox1.Text = Me.lstsearch.Column(0)
    UserForm3.TextBox2.Text = Me.lstsearch.Column(1)
    UserForm3.TextBox3.Text = Me.lstsearch.Column(7)
    UserForm3.TextBox4.Text = Me.lstsearch.Column(4)
    UserForm3.TextBox5.Text = Me.lstsearch.Column(3)
    UserForm3.TextBox6.Text = Me.lstsearch.Column(5)
    UserForm3.TextBox7.Text = Me.lstsearch.Column(6)
    UserForm3.Show

End Sub

Userform2 code. for edit

Private Sub CommandButton1_Click()

    UserForm1.lstsearch.Column(1) = Me.TextBox4.Text
    UserForm1.lstsearch.Column(5) = Me.TextBox2.Text
    UserForm1.lstsearch.Column(6) = Me.TextBox3.Text
    UserForm1.lstsearch.Column(0) = Me.TextBox5.Text
    UserForm1.lstsearch.Column(3) = Me.TextBox6.Text
    
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox4.Text = ""
    TextBox6.Text = ""
    TextBox5.Text = ""
End Sub

thanks in advanced

 

[LegenDSlayeR]

userform1 Coding

Private Sub cmdclear_Click()
    txtproductcode.Text = ""
    txtbatchnumber.Text = ""
    txtexpirydate.Text = ""
    txtquantity.Text = ""
    txtairlocation.Text = ""
    
End Sub

Private Sub cmdsave_Click()
ThisWorkbook.Save

End Sub

Private Sub CommandButton1_Click()
    Dim RowNum As Long
        RowNum = 2
            Do Until Sheets("airstock").Cells(RowNum, 1).Value = ""
            
                If InStr(1, Sheets("airstock").Cells(RowNum, 1).Value, txtsearch.Value, vbTextCompare) > 0 Then
                    On Erro GoTo next1
                    lstsearch.AddItem Sheets("airstock").Cells(RowNum, 1).Value
                    lstsearch.List(lstsearch.ListCount - 1, 1) = Sheets("airstock").Cells(RowNum, 2).Value
                    lstsearch.List(lstsearch.ListCount - 1, 2) = Sheets("airstock").Cells(RowNum, 3).Value
                    lstsearch.List(lstsearch.ListCount - 1, 3) = Sheets("airstock").Cells(RowNum, 4).Value
                    lstsearch.List(lstsearch.ListCount - 1, 4) = Sheets("airstock").Cells(RowNum, 5).Text
                    lstsearch.List(lstsearch.ListCount - 1, 5) = Sheets("airstock").Cells(RowNum, 6).Value
                    lstsearch.List(lstsearch.ListCount - 1, 6) = Sheets("airstock").Cells(RowNum, 7).Value
                    lstsearch.List(lstsearch.ListCount - 1, 7) = Sheets("airstock").Cells(RowNum, 8).Value
End If

next1:
    RowNum = RowNum + 1
    Loop
    lstsearch.RowSource = ""
    
End Sub

Private Sub CommandButton2_Click()
 Dim i   As Range
    
    If MsgBox("Are You Sure You Want to Remove This Pallet? ", vbQuestion + vbYesNo) = vbYes Then
        For Each i In ActiveSheet.Range("a3:a10209")
            If i = Me.lstsearch.Value Then
                i.EntireRow.Delete
                Exit For
            End If
        Next i
        
        MsgBox "All Done"
     End If
     txtsearch.Text = ""
  End Sub

Private Sub CommandButton4_Click()
    Dim wks As Worksheet
        Dim AddNew As Range
        Set wks = airstock

            Set AddNew = wks.Range("A10209").End(xlUp).Offset(1, 0)
            AddNew.Offset(0, 0).Value = txtproductcode.Text
            AddNew.Offset(0, 3).Value = txtbatchnumber.Text
            AddNew.Offset(0, 4).Value = txtexpirydate.Text
            AddNew.Offset(0, 5).Value = txtquantity.Text
            AddNew.Offset(0, 6).Value = txtairlocation.Text
    
    
         
End Sub

Private Sub CommandButton6_Click()
    lstsearch.Clear
    txtsearch.Text = ""

End Sub

Private Sub CommandButton7_Click()
    ThisWorkbook.Save
End Sub

Private Sub CommandButton9_Click()
    Dim iexit As VbMsgBoxResult
        iexit = MsgBox("Are You Sure Want to Exit?", vbQuestion + vbYesNo, "Air Stock Locator")
            If iexit = vbYes Then
                Unload Me
        End If
End Sub

Private Sub lstsearch_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm2.TextBox2.Text = Me.lstsearch.Column(5)
    UserForm2.TextBox4.Text = Me.lstsearch.Column(1)
    UserForm2.TextBox3.Text = Me.lstsearch.Column(6)
    UserForm2.TextBox5.Text = Me.lstsearch.Column(0)
    UserForm2.TextBox6.Text = Me.lstsearch.Column(3)
    UserForm2.Show
    
    UserForm3.TextBox1.Text = Me.lstsearch.Column(0)
    UserForm3.TextBox2.Text = Me.lstsearch.Column(1)
    UserForm3.TextBox3.Text = Me.lstsearch.Column(7)
    UserForm3.TextBox4.Text = Me.lstsearch.Column(4)
    UserForm3.TextBox5.Text = Me.lstsearch.Column(3)
    UserForm3.TextBox6.Text = Me.lstsearch.Column(5)
    UserForm3.TextBox7.Text = Me.lstsearch.Column(6)
    UserForm3.Show

End Sub

Private Sub txtairlocation_Change()
    txtairlocation.Text = UCase(txtairlocation.Text)
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub UserForm_Initialize()
lstdisplay.ColumnCount = 8
lstdisplay.RowSource = "airstock! A2: J10209"

End Sub

userform2 coding

Private Sub CommandButton1_Click()
Dim i As Range
If MsgBox("Are you Sure you want to make changes to this pallet?", vbQuestion + vbYesNo) = vbYes Then
    For Each i In ActiveSheet.Range("F2:G10209")
    Sheets("airstock").Select

    i = i + 1


    Rows(i).Select


    Cells(i, 5) = Me.TextBox2.Value


    Cells(i, 6) = Me.TextBox3.Value
    
    Next i
    


    UserForm1.lstsearch.Column(1) = Me.TextBox4.Text
    UserForm1.lstsearch.Column(5) = Me.TextBox2.Text
    UserForm1.lstsearch.Column(6) = Me.TextBox3.Text
    UserForm1.lstsearch.Column(0) = Me.TextBox5.Text
    UserForm1.lstsearch.Column(3) = Me.TextBox6.Text
    
    TextBox2.Text = ""
    TextBox3.Text = ""
    TextBox4.Text = ""
    TextBox6.Text = ""
    TextBox5.Text = ""
End Sub


Private Sub TextBox2_Change()

End Sub

Private Sub TextBox3_Change()
TextBox3.Text = UCase(TextBox3.Text)
End Sub

Private Sub TextBox5_Change()

End Sub

Private Sub TextBox6_Change()

End Sub

 

[My Aswer Is This]

You have a lot of coding here and I'm not sure what coding is doing what you want and which part of the coding does not work. And I'm surprised you would need more then one Userform to perform any task. I have never needed more the one Userform to perform a task. Have you ever tried using a Multipage. You should see this in your tool box.
It's like having numerous Userforms all on one Userform.
Sort of like numerous pages in a book.
And do you know by opening up your userform like this:
Userform1.show Modeless
You can work on your sheets while having the Userform displayed on the screen.

 

[LegenDSlayeR]

You have a lot of coding here and I'm not sure what coding is doing what you want and which part of the coding does not work. And I'm surprised you would need more then one Userform to perform any task. I have never needed more the one Userform to perform a task. Have you ever tried using a Multipage. You should see this in your tool box.
It's like having numerous Userforms all on one Userform.
Sort of like numerous pages in a book.
And do you know by opening up your userform like this:
Userform1.show Modeless
You can work on your sheets while having the Userform displayed on the screen.

userform2 which has the update changes. i want to be able to update the worksheet (Airstock) by updating only 2 text boxes Quantity and Air location. im newbie at all this and this is my first ever time using VBA. hope that makes sense.

 

[My Aswer Is This]

You would have to enter the new text in your TextBox and then click on a Command button and tell the script where to enter the new data. Sort of like this.
Sheets("Alpha").Range("B14").value=Textbox1.value