View original (French)

Button_Edit_select_listBox

Solved
zouzou -  

Hello,

I would like to modify a value in an Excel table from a listBox. Let me explain,

when I select a value from the listBox, the textboxes get filled, I modify a textbox and click on the modify button (to modify my data) but it does not change (it's the first value of the table that gets modified). I would like to modify the selected value in the listBox!

Could you help me write the code!

Attached is my current code

Thank you very much

Private Sub b_modif_Click() Dim ligne As Integer If MsgBox("Do you confirm the modification?", vbYesNo, "Modification Confirmation") <> vbYes Then ligne = ListBox7.ListIndex + 2 MsgBox ligne Range("A" & ligne) = T_Id.Value Range("B" & ligne) = T_Designation.Value Range("C" & ligne) = T_Code.Value Range("D" & ligne) = T_Stock_Min.Value Range("E" & ligne) = T_Nvx_Emplt.Value Range("F" & ligne) = T_Fournisseur.Value Range("G" & ligne) = T_Stock_Reel.Value Range("H" & ligne) = T_Stock_Max.Value Range("I" & ligne) = T_Ancien_Emplt.Value Range("J" & ligne) = T_Machine.Value Range("K" & ligne) = ComboBox1.Value Range("L" & ligne) = ComboBox2.Value Range("M" & ligne) = ComboBox3.Value Range("N" & ligne) = ComboBox4.Value Range("O" & ligne) = ComboBox5.Value End If End Sub


11 answers

  1.  yg_be Contributor

    Hello,

    can you also show the code that reacts when you select a data in the listBox?

    1. zouzou

      Hello yg_be

      here is the code when I click on the listbox

      thank you very much for your help

      Private Sub ListBox7_Click() Me.T_Id = Me.ListBox7.List(Me.ListBox7.ListIndex) Me.T_Designation.Value = Me.ListBox7.Column(1, Me.ListBox7.ListIndex) Me.T_Code.Value = Me.ListBox7.Column(2, Me.ListBox7.ListIndex) Me.T_Stock_Min.Value = Me.ListBox7.Column(3, Me.ListBox7.ListIndex) Me.T_Nvx_Emplt.Value = Me.ListBox7.Column(4, Me.ListBox7.ListIndex) Me.T_Fournisseur.Value = Me.ListBox7.Column(5, Me.ListBox7.ListIndex) Me.T_Stock_Reel.Value = Me.ListBox7.Column(6, Me.ListBox7.ListIndex) Me.T_Stock_Max.Value = Me.ListBox7.Column(7, Me.ListBox7.ListIndex) Me.T_Ancien_Emplt.Value = Me.ListBox7.Column(8, Me.ListBox7.ListIndex) Me.T_Machine.Value = Me.ListBox7.Column(9, Me.ListBox7.ListIndex) Me.ComboBox1.Value = Me.ListBox7.Column(10, Me.ListBox7.ListIndex) Me.ComboBox2.Value = Me.ListBox7.Column(11, Me.ListBox7.ListIndex) Me.ComboBox3.Value = Me.ListBox7.Column(12, Me.ListBox7.ListIndex) Me.ComboBox4.Value = Me.ListBox7.Column(13, Me.ListBox7.ListIndex) Me.ComboBox5.Value = Me.ListBox7.Column(14, Me.ListBox7.ListIndex) End Sub
      1.  yg_be Contributor

        Hello,

        I don’t understand why "ListBox7.ListIndex" no longer contains the correct value in "b_modif_Click()".
        I would add a control to store its value. Suppose this added control is called ListX.

        Then, in "ListBox7_Click()", I would add:

        Me.ListX = Me.ListBox7.ListIndex

        And in "b_modif_Click", I would add:

        ligne=ListX
    2. zouzou

      Hello yg_be,

      I set line = ListBox7.ListIndex + 2, because my data starts from the second line of the table.

      I would like to know, please, what type is the ListX you added? (I didn't quite understand why you put "Me.")

      When I add in ListBox7_Click(),

       Me.ListX = Me.ListBox7.ListIndex

      And in "b_modif_Click", I would add:

       line=ListX

      it shows me the error above

      Thank you very much in advance

      1.  yg_be Contributor

        Did you add a control named ListX?

    3. zouzou 
      • Private Sub b_modif_Click() Dim ligne As Integer If MsgBox("Do you confirm the modification?", vbYesNo, "Modification Confirmation") <> vbYes Then ligne = ListX.Value 'ligne = ListBox7.ListIndex + 2 MsgBox ligne Range("A" & ligne) = Me.T_Id.Value Range("B" & ligne) = Me.T_Designation.Value Range("C" & ligne) = Me.T_Code.Value Range("D" & ligne) = Me.T_Stock_Min.Value Range("E" & ligne) = Me.T_Nvx_Emplt.Value Range("F" & ligne) = Me.T_Fournisseur.Value Range("G" & ligne) = Me.T_Stock_Reel.Value Range("H" & ligne) = Me.T_Stock_Max.Value Range("I" & ligne) = Me.T_Ancien_Emplt.Value Range("J" & ligne) = Me.T_Machine.Value Range("K" & ligne) = Me.ComboBox1.Value Range("L" & ligne) = Me.ComboBox2.Value Range("M" & ligne) = Me.ComboBox3.Value Range("N" & ligne) = Me.ComboBox4.Value Range("O" & ligne) = Me.ComboBox5.Value End If End Sub
      Private Sub ListBox7_DblClick(ByVal Cancel As MSForms.ReturnBoolean) If T_Fournisseur.Value = "" And T_Designation.Value = "" Then MsgBox "Please enter a name!!" Exit Sub End If If T_Designation.Value <> "" Then Set cel = Feuil2.Columns(1).Find(what:=ListBox7.Value, LookIn:=xlValues, lookat:=xlWhole) If Not cel Is Nothing Then Me.SpinButton2 = cel.Row End If End If btncreer.Enabled = False End Sub Private Sub ListBox7_Click() Me.ListX = Me.ListBox7.ListIndex Me.T_Id = Me.ListBox7.List(Me.ListBox7.ListIndex) Me.T_Designation.Value = Me.ListBox7.Column(1, Me.ListBox7.ListIndex) Me.T_Code.Value = Me.ListBox7.Column(2, Me.ListBox7.ListIndex) Me.T_Stock_Min.Value = Me.ListBox7.Column(3, Me.ListBox7.ListIndex) Me.T_Nvx_Emplt.Value = Me.ListBox7.Column(4, Me.ListBox7.ListIndex) Me.T_Fournisseur.Value = Me.ListBox7.Column(5, Me.ListBox7.ListIndex) Me.T_Stock_Reel.Value = Me.ListBox7.Column(6, Me.ListBox7.ListIndex) Me.T_Stock_Max.Value = Me.ListBox7.Column(7, Me.ListBox7.ListIndex) Me.T_Ancien_Emplt.Value = Me.ListBox7.Column(8, Me.ListBox7.ListIndex) Me.T_Machine.Value = Me.ListBox7.Column(9, Me.ListBox7.ListIndex) Me.ComboBox1.Value = Me.ListBox7.Column(10, Me.ListBox7.ListIndex) Me.ComboBox2.Value = Me.ListBox7.Column(11, Me.ListBox7.ListIndex) Me.ComboBox3.Value = Me.ListBox7.Column(12, Me.ListBox7.ListIndex) Me.ComboBox4.Value = Me.ListBox7.Column(13, Me.ListBox7.ListIndex) Me.ComboBox5.Value = Me.ListBox7.Column(14, Me.ListBox7.ListIndex) End Sub

      Hello,

      I added a textbox to the userform, I named it ListX, here are my two codes for the modify button and the listbox, as well as the simulation.

      The ListX highlighted in blue shows the line number in the listbox (the line in the listbox is not the line on the Excel table!) so I can't assign the value of ListX to the line variable in my modify button code!

      Thank you very much in advance.

      2. zouzou

        I think I will use the same trick you showed me with (the deleted button) to test if the IDs are equal and then modify instead of deleting, it seems like a good trick to me.

        1. zouzou 
          Private Sub b_modif_Click() 
    Dim ligne As Integer 
    Dim i As Long, suppression As String, present As String 
    If MsgBox("Confirmez-vous la modification ?", vbYesNo, "Confirmation de modification") <> vbYes Then 
        With ThisWorkbook.Sheets("Electrique") 
            For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1 
                present = .Range("A" & i).Value 
                If present = Me.T_Id.Value Then 
                    ligne = i ' Assigner la ligne actuelle à ligne pour éviter de l'utiliser sans valeur
                    Range("A" & ligne) = Me.T_Id.Value 
                    Range("B" & ligne) = Me.T_Designation.Value 
                    Range("C" & ligne) = Me.T_Code.Value 
                    Range("D" & ligne) = Me.T_Stock_Min.Value 
                    Range("E" & ligne) = Me.T_Nvx_Emplt.Value 
                    Range("F" & ligne) = Me.T_Fournisseur.Value 
                    Range("G" & ligne) = Me.T_Stock_Reel.Value 
                    Range("H" & ligne) = Me.T_Stock_Max.Value 
                    Range("I" & ligne) = Me.T_Ancien_Emplt.Value 
                    Range("J" & ligne) = Me.T_Machine.Value 
                    Range("K" & ligne) = Me.ComboBox1.Value 
                    Range("L" & ligne) = Me.ComboBox2.Value 
                    Range("M" & ligne) = Me.ComboBox3.Value 
                    Range("N" & ligne) = Me.ComboBox4.Value 
                    Range("O" & ligne) = Me.ComboBox5.Value 
                End If 
            Next i 
        End With 
    End If 
End Sub

          je pense que ce code doit passer ! par contre je compile, ça m'affiche :

          end with sans with

          end if sans bloc if ...

          je ne comprends pas l'erreur

          merci beaucoup d'avance

        2.  yg_be Contributor
          @zouzou

          First, you need to close the last open element.
          Since the code starts with

          • If,
          • With,
          • For,
          • If,

          it must end with

          • end if
          • next
          • end with
          • end if

          Moreover, you are using two variables, i and line, while only one is sufficient.

      3. zouzou

        Thank you very much yg_be,

        I'm sharing with you my code that should normally work, it's the same trick I used on the deleted button! In your opinion, why isn't it modifying the selection and showing no error! The code is correct.

        Thank you very much in advance

        Private Sub b_modif_Click() Dim i As Long, modification As String, present As String If MsgBox("Do you confirm the modification?", vbYesNo, "Modification Confirmation") <> vbYes Then With ThisWorkbook.Sheets("Electrique") For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1 present = .Range("A" & i).Value modification = Me.T_Id.Value If present = modification Then modification = modification + 1 Range("A" & modification) = Me.T_Id.Value Range("B" & modification) = Me.T_Designation.Value Range("C" & modification) = Me.T_Code.Value Range("D" & modification) = Me.T_Stock_Min.Value Range("E" & modification) = Me.T_Nvx_Emplt.Value Range("F" & modification) = Me.T_Fournisseur.Value Range("G" & modification) = Me.T_Stock_Reel.Value Range("H" & modification) = Me.T_Stock_Max.Value Range("I" & modification) = Me.T_Ancien_Emplt.Value Range("J" & modification) = Me.T_Machine.Value Range("K" & modification) = Me.ComboBox1.Value Range("L" & modification) = Me.ComboBox2.Value Range("M" & modification) = Me.ComboBox3.Value Range("N" & modification) = Me.ComboBox4.Value Range("O" & modification) = Me.ComboBox5.Value End If Next i End With End If End Sub

        :

        1.  yg_be Contributor

          maybe:

           For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1 If .Range("A" & i).Value = Me.T_Id.Value Then .Range("B" & i) = Me.T_Designation.Value ' ...
      4. zouzou

        Thank you for the suggestion, I tested it, it is correct but it doesn't give any results!

        Private Sub b_modif_Click() Dim i As Long, modification As String, present As String If MsgBox("Do you confirm the modification?", vbYesNo, "Modification Confirmation") <> vbYes Then With ThisWorkbook.Sheets("Electrique") For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1 If .Range("A" & i).Value = Me.T_Id.Value Then .Range("B" & i) = Me.T_Designation.Value .Range("C" & i) = Me.T_Code.Value .Range("D" & i) = Me.T_Stock_Min.Value .Range("E" & i) = Me.T_Nvx_Emplt.Value .Range("F" & i) = Me.T_Fournisseur.Value .Range("G" & i) = Me.T_Stock_Reel.Value .Range("H" & i) = Me.T_Stock_Max.Value .Range("I" & i) = Me.T_Ancien_Emplt.Value .Range("J" & i) = Me.T_Machine.Value .Range("K" & i) = Me.ComboBox1.Value .Range("L" & i) = Me.ComboBox2.Value .Range("M" & i) = Me.ComboBox3.Value .Range("N" & i) = Me.ComboBox4.Value .Range("O" & i) = Me.ComboBox5.Value ' present = .Range("A" & i).Value ' modification = Me.T_Id.Value ' If present = modification Then ' modification = modification + 1 'Range("A" & modification) = Me.T_Id.Value 'Range("B" & modification) = Me.T_Designation.Value End If Next i End With End If End Sub
        2. mzouali
          @yg_be

          Hello yg_be,

          The msgBox after the if doesn't work and the one before the if does.

          Private Sub b_modif_Click() Dim i As Long, modif As String, present As String If MsgBox("Do you confirm the modification?", vbYesNo, "Modification Confirmation") = vbYes Then With ThisWorkbook.Sheets("Electrique") For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1 If .Range("A" & i).Value = Me.T_Id.Value Then MsgBox ("modification confirmed")
        3.  yg_be Contributor
          @mzouali

          maybe add before the first "end if"

          else msgbox .Range("A" & i).Value & "|" & Me.T_Id.Value

          This should show the compared values and confirm that they are different.

        4. mzouali
          @yg_be

          yes, indeed, they are different

          here is the result in a loop 9|1 then 8|1 7|1 6|1 5|1 4|1 3|1 2|1 1|1 :

        5.  yg_be Contributor
          @mzouali

          Strange that 1|1 is so different...

      5. ziz

        Hello,

        I am getting back to you to solve the same problem with the edit button; I had a move that prevented me from progressing.

        Result: when the two values Range("A" & i).Value and Me.T_Id.Value are equal, it continues to compare without modifying the cell value!

        Private Sub b_modif_Click() Dim i As Long, modification As String, present As String With ThisWorkbook.Sheets("Electrique") For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1 If Range("A" & i).Value = Me.T_Id.Value Then MsgBox .Range("A" & i).Value & "|" & Me.T_Id.Value Range("B" & i) = Me.T_Designation.Value Else MsgBox .Range("A" & i).Value & "|" & Me.T_Id.Value End If Next i End With 'End If End Sub

        It goes through the equality and continues to compare

        until it reaches the very top

        Do you have any idea how to modify the content of a cell?

        Thank you in advance

        1.  yg_be Contributor

          The most interesting thing is that you managed to create two msgboxes that don't allow you to differentiate between the two conditions.

      6. ziz

        Thank you for your comments, I kept only the first msgBox and nothing is displayed

         With ThisWorkbook.Sheets("Electrique") For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1 If Range("A" & i).Value = Me.T_Id.Value Then MsgBox .Range("A" & i).Value & "|" & Me.T_Id.Value Range("B" & i) = Me.T_Designation.Value Else 'MsgBox .Range("A" & i).Value & "|" & Me.T_Id.Value
        1.  yg_be Contributor

          try instead:

          If Range("A" & i).Value = Me.T_Id.Value Then MsgBox .Range("A" & i).Value & " = " & Me.T_Id.Value Range("B" & i) = Me.T_Designation.Value Else MsgBox .Range("A" & i).Value & " <> " & Me.T_Id.Value
      7. ziz

        it gives the same result:

        it continues to compare until it compares the whole table...

        1.  yg_be Contributor

          I think the solution is in #21.

      8. ziz

        Hello yg_be

        What do you mean by #21?

        Finally, I managed to modify the table by using a modify button:

        Here is the code:

         With ThisWorkbook.Sheets("Electrique") ' I define the sheet where I work For i = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1 ' a loop that processes all the rows of my table. If Cells(i, 1) = Me.T_Id.Text Then ' comparing all the rows of my first column with the value of the textbox named "T_Id", if they are equal then it selects the cell to the right and modifies the value of that cell to the value of T_Designation "textbox from the userform". Cells(i, 1).Select Exit For End If Next i ActiveCell.Offset(0, 1) = Me.T_Designation.Value ' modification

        Thank you very much yr_be

        1.  yg_be Contributor

          #21, this is the response made on July 27, 2022 at 9:18 PM.