Is it possible to edit data in the ListBox? [closed]

1

Double-clicking the row to be edited from the ListBox opens the UserForm with the data of the selected row. I can not make the changes to be modified in the ListBox and the Spreadsheet. Code when starting the UserForm:

Private Sub UserForm_Initialize()

Dim Item As Integer

 For Item = 0 To UserForm.ListBox1.ListCount - 1

       If UserForm.ListBox1.Selected(Item) = True Then

        TextBox1 = UserForm.ListBox1.List(Item, 0)
        TextBox2 = UserForm.ListBox1.List(Item, 1)
        TextBox3 = UserForm.ListBox1.List(Item, 2)
        TextBox4 = UserForm.ListBox1.List(Item, 3)
        TextBox5 = UserForm.ListBox1.List(Item, 4)
        TextBox6 = UserForm.ListBox1.List(Item, 5)
        TextBox7 = UserForm.ListBox1.List(Item, 6)
        TextBox8 = UserForm.ListBox1.List(Item, 7)
        TextBox9 = UserForm.ListBox1.List(Item, 8)

       End If

 Next

End Sub  
    
asked by anonymous 12.07.2016 / 18:52

1 answer

5

Yes, it is possible. Just change the value of the List property in the index of the selected object. Here is an example code with two UserForms (one called "Test", which displays the ListBox, and another called "Edit" that displays the contents of the currently selected item in the list and allows you to edit it):

UserForm "Test"

Screenshot:

(TheListBoxiscalled"ListBox", the "Test" UserForm is created only once and reused)

Code:

Dim oEditForm As Editar

Private Sub ListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim sName As String

    sName = Me.ListBox.List(Me.ListBox.ListIndex)
    oEditForm.EditName.Text = sName
    oEditForm.Show
    If oEditForm.Tag = True Then
        sName = oEditForm.EditName.Text
        Me.ListBox.List(Me.ListBox.ListIndex) = sName
    End If

End Sub

Private Sub UserForm_Initialize()

    Set oEditForm = New Editar

    Me.ListBox.AddItem "Tyrion Lannister"
    Me.ListBox.AddItem "Jaime Lannister"
    Me.ListBox.AddItem "Cersei Lannister"
    Me.ListBox.AddItem "Daenerys Targaryen"
    Me.ListBox.AddItem "Jon Snow"
    Me.ListBox.AddItem "Petyr Baelish"
    Me.ListBox.AddItem "Jorah Mormont"

End Sub

UserForm "Edit"

Screenshot:

(thebuttonsarecalled"Ok" and "Cancel", and the TextBox is called "EditName")

Code:

Private Sub Cancel_Click()
    Me.Tag = False
    Hide
End Sub

Private Sub Ok_Click()
    Me.Tag = True
    Hide
End Sub

Result

Screenshot:

Explanation of the code:

  • First you need to capture the appropriate double click event, called *_DblClick .
  • Then get the text of the currently selected item in the list from ListBox.List by passing its index that is in ListBox.ListIndex .
  • Put this value in your other edit form and allow the user to edit it. When it clicks Ok, go to the next step.
  • Having the user edit the value, update the content of the same currently selected item in the list (that is, using ListBox.List passing its index that is in ListBox.ListIndex ). >
  • 14.07.2016 / 00:02