Change listbox value

Hello,

I have created a sheet with a rowsource from A1:L19 in sheet1

What i want to do is change the value's in a userform. I have an example of how i like to see it.

http://www.excelforum.com/excel-prog...x-records.html

But in that case i need to create for every cell a separate text box. Is there a easier way to manage/change this? I only need to have the buttons edit and change. How can i manage this?
See attached file.Attachment 422

can you please post the link again as it seems broken.

Quote:

---

Originally Posted by Admin

can you please post the link again as it seems broken.

---

Edit Add Delete Listbox Records

This should help. I've added textboxes to a second userform during run time when you click on one row in the first userform.

Here's the code for reference

Code:

---

Public var As Variant
Dim rng As Range
Private Sub ListBox1_Click()

    UserForm2.Show 0
   
End Sub

Private Sub UserForm_Initialize()

    With ListBox1
        Set rng = Worksheets(Replace(Split(.RowSource, "!")(0), "'", "")).Range(Split(.RowSource, "!")(1))
'If your row source doesn't have reference to a sheet, but only to a range, just use Set rng = Range(RowSource)
    var = rng
        .RowSource = ""
        .List = var
    End With
   
End Sub

Private Sub UserForm_Terminate()

    rng.Value = var
   
End Sub

---

Code:

---

Dim txtSampleTextBox() As MSForms.TextBox
Private Sub UserForm_Initialize()
   
    Dim lng As Long
    Dim obj As MSForms.TextBox
    lng = UserForm1.ListBox1.ColumnCount
    ReDim Preserve txtSampleTextBox(1 To lng)
   
    For lng = 1 To lng
        Set txtSampleTextBox(lng) = Controls.Add("Forms.TextBox.1", "txtSampleTextBox" & lng)
        With txtSampleTextBox(lng)
            .Left = (lng - 1) * .Width + 5
            .Text = UserForm1.ListBox1.List(UserForm1.ListBox1.ListIndex, lng - 1)
        End With
    Next lng
    Me.Width = (lng - 1) * (txtSampleTextBox(lng - 1).Width + 1)

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    Dim lng As Long
    With UserForm1.ListBox1
        For lng = 1 To Me.Controls.Count
            .List(.ListIndex, lng - 1) = Controls("txtSampleTextBox" & lng).Text
        Next lng
        UserForm1.var = .List
    End With
   
End Sub

---

How can i add this to a multipage? in one userform i have a multipage with 5 tabs. Each tab has it's own listbox connected to a sheet. And is it also possible to make the changescreen fixed under the listbox directly? I only want to show a textbox when there is a value in the sheet