Edit ListBox

[sixman]

Have a question. I have a userform with multiple TextBoxes and a ListBox. The TextBoxes are your standard Name, Address, City, Phone. I enter the data into the TextBoxes and click an OK button and the data is stored on an active sheet called Database and the information is also displayed in my ListBox. This works fine. What I would like to be able to do is highlight an item in my ListBox and click an "Edit" button and recall the data that is stored on my "Database" back into my TextBoxes. Then I could edit either Name, Address, City, Phone and resubmit the data back into the database. Any Help would be greatly appreciated.

 

[TryingToLearn]

Private Sub CommandButton1_Click()
    Dim nameAddress As Variant

    nameAddress = Sheets("sheet1").Cells.Find(What:=ListBox1.Value, LookIn:=xlFormulas, _
                                              LookAt:=1, SearchDirection:=xlNext, MatchCase:=False).Offset(0, 0).Address
    With Sheets("sheet1").Range(nameAddress)
        TextBox1.Value = .Offset(0, 0)
        TextBox2.Value = .Offset(0, 1)
        TextBox3.Value = .Offset(0, 2)
        TextBox4.Value = .Offset(0, 3)
    End With
End Sub

 

[sixman]

This snippet of code works well, but there is some intrinsic information that I left out of my original post. I did want the Listbox data to be restored into the textboxes in my userform but I wanted to be able to edit the info and resubmit it back into the ListBox in the same place it was highlighted. The way this code works is that it just puts the data from the listbox into the textboxes and leaves the old data still showing in the listbox. I would also like the listbox to refresh. The data I enter goes to my Database but the listbox stops filling up after about 5-6 entries.

 

[TryingToLearn]

You can add another commandbutton (I assume you have an OK commandbutton to enter the data in the first place and now one labeled EDIT or some such) labeled ACCEPT CHANGES that will reverse the code I gave you to place the value of the textboxes back into the addresses and offsets they came from.

How are you setting the parameters for the listbox ListFillRange? It should be dynamic to grow as your entries do.

 

[sixman]

You were right about the reversal it worked perfect thanks. I am still having trouble refreshing my listbox. I key in my entries and they don't show up in the listbox, but if I close the Userform containing my listbox and then reopen it, the data will show up. Not sure what is getting lost in translation.

 

[TryingToLearn]

At the end of your CHANGE code, add a line to set the listbox rowsource. This should refresh it.

or just put the code in the listbox itself such as:


Private Sub ListBox1_Enter()
ListBox1.RowSource = Sheets("sheet1").Range("MYRANGE")
End Sub

HTH

 

[sixman]

I fixed what was I presume was the problem with my listbox. Since we are on the subject, can you advise me of a good way to delete selected rows in my listbox that is populated from a spreadsheet. I have a "crude" delete button but I need it to delete the data and shift all those cells up. So far all it can do is delete the contents in the cells. The last problem I have is not such a problem but more of advice on the best way to populate the listbox with my textboxes. What I have now works fine, but I feel something behind the scenes is going awry. As my Listbox grows larger and larger the transaction time gets slower and slower. I have a feeling my coding is weak and could use a good clean-up