Is it possible to edit Listbox data?

Asked

Viewed 3,337 times

1

When double-clicking on the line to be edited from the Listbox opens the Userform with the data of the selected line. I cannot get the changes to be modified in Listbox and Spreadsheet. Code when starting 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
excel vba

  • Hello. It’s not clear what the problem is. Would you like to put in the list the values typed by the user in the Textboxes, is that it? Another thing: why the hell you make a loop is if you take items one by one?

  • I am trying to edit data by Lixtbox, so in the double click opens Userform and the textboxes are loaded with the values of the selected Lixtbox line. I’m not getting these values changed after editing. How much the loop I thought would solve, was the last attempt I made. I’m a layman on the subject.

  • Got it. It’s really not at all clear on the question. How about adding some screenshots of your running project? Another thing: you mention "spreadsheet", but by the way just want to know how to update the value of a line in a Listbox (and not in the spreadsheet! and so he got an answer that didn’t help you).

  • 1

    I responded on the basis as far as I understand than you described in comment. But I will keep my vote to close until you edit the question and make it clear there, ok?

  • Luiz good morning! You reported another item that had not paid attention that is save the change in the spreadsheet as well. Because when doing a research in the future will already be arranged. But saving in the spreadsheet is very difficult for me to adjust the code I believe.

  • 1

    Dude, I’m sorry, but I don’t understand what you’re saying. " Saving in the spreadsheet is very difficult for me (sic) to adjust the code" -> this was a comment or a question?

  • It was a comment. I’m not getting missing the "New Edit". I can’t attach the screen print!

  • If you have another question, you should open a new question. Read [Ask]. ;)

Show 3 more comments

1 answer

5

Yes, it is possible. Simply change the property value List in the index of the selected object. Here is an example of code with two Userforms (one called "Test", which displays the Listbox, and another called "Edit" which displays the contents of the currently selected item from the list and lets you edit it):

Userform "Testing"

Screen image:

inserir a descrição da imagem aqui (Listbox is called "Listbox"; Userform "Test" is created 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"

Screen image:

inserir a descrição da imagem aqui (the buttons are called "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

Upshot

Screen image:

inserir a descrição da imagem aqui

Explanation of the code:

  1. First you need to capture the appropriate double click event, called *_DblClick.
  2. Then, get the text of the item currently selected in the list from ListBox.List passing his index which is in ListBox.ListIndex.
  3. Put this value in your other edit form and allow the user to edit it. When they click Ok, go to the next step.
  4. Having the user edited the value, update the content of the same item currently selected in the list (that is, using ListBox.List passing his index which is in ListBox.ListIndex).

Browser other questions tagged excel vba

You are not signed in. Login or sign up in order to post.