make changes to a Row

LegenDSlayeR

New Member
Joined
Nov 26, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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

Code:
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

LegenDSlayeR

New Member
Joined
Nov 26, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
userform1 Coding
VBA Code:
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

VBA Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,265
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 26, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,265
Office Version
  1. 2013
Platform
  1. Windows
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,595
Members
414,462
Latest member
StageRiis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top