View original (French)

Button_Edit_select_listBox

Solved
zouzou -  
yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   -

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 Posted messages 23437 Registration date   Status Contributor Last intervention   Ambassadeur 1 588
     

    Hello,

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

    0
  2. 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
    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      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
      0
  3. 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

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      Did you add a control named ListX?

      0
  4. 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.

    0
  6. 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.

    0
    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

      0
      1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > 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.

        0
  7. 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

    :

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      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 ' ...
      0
  8. 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
    0
    2. mzouali Posted messages 22 Status Member > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
       

      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")
      0
    3. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > mzouali Posted messages 22 Status Member
       

      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.

      1
    4. mzouali Posted messages 22 Status Member > yg_be Posted messages 23437 Registration date   Status Contributor Last intervention  
       

      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 :

      0
    5. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588 > mzouali Posted messages 22 Status Member
       

      Strange that 1|1 is so different...

      1
  9. 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

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

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

      0
  10. 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
    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      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
      0
  11. ziz
     

    it gives the same result:

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

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

      I think the solution is in #21.

      0
  12. 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

    0
    1. yg_be Posted messages 23437 Registration date   Status Contributor Last intervention   1 588
       

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

      0