How to Change Data within a ListBox Control

Question

_{Saturday, October 13, 2012 11:06 PM}

Excel 2010.  VBA.  Userform.

I have a userform that has a listbox control on it.  The listbox control's rowsource is a range of cells on a worsheet in the Excel workbook.  The data displays fine and I am able to select a given row.  But what I really want to do is to be able to edit the individual cells within the listbox and have the corresponding data on the worksheet updated as well.  How can I accomplish this?

Doug Pruiett Good News Jail & Prison Ministry Richmond, Virginia www.goodnewsjail.org

All replies (4)

_{Monday, October 15, 2012 3:16 AM ✅Answered}

Hi,

I agree with Oskar, the Listbox cannot be update. So what about using a textbox and a button?

See the picture below:

Select the value you want to update in the listbox, then type in the value you want to change in the textbox, click the button, then the value in the worksheet will change automatically.

Note: In this example, the range of the value is A1:A7, change position of the range in this code: Cells(ListBox1.ListIndex + 1, 1).Value. ListBox1.ListIndex indicates the row index, the number “1” after the comma indicates the column A.

Here’s the code:

Private Sub CommandButton1_Click()

Dim text_update As String

text_update = TextBox1.Value

Cells(ListBox1.ListIndex + 1, 1).Value = text_update

End Sub

Jaynet Zhang

TechNet Community Support

_{Monday, October 15, 2012 2:12 PM ✅Answered}

Thank you for the example.  I did something like that for the solution.

Doug Pruiett Good News Jail & Prison Ministry Richmond, Virginia www.goodnewsjail.org

_{Monday, October 15, 2012 3:20 PM ✅Answered}

Note:  The textboxes to the right of the listboxes are dynamically tied to the cells associated with the selected row in the listbox.  The ListBox_Change event routine updates the textbox controlsource property to point to the cell in an underlying worksheet.  Thus, when the textboxes are updated, the underlying cells in the worksheet are updated.  Thanks.

Doug Pruiett Good News Jail & Prison Ministry Richmond, Virginia www.goodnewsjail.org

_{Sunday, October 14, 2012 10:28 PM}

As far i know, You cant. Listbox is only for viewing control. You should to use inputbox and selection operation to Chance record in listbox.

Oskar Shon, Office System MVP

Press if Helpful; Answer when a problem solved