Change listbox value

[Tony]

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.422

[Admin]

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

[Tony]

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

Edit Add Delete Listbox Records (http://www.excelforum.com/excel-programming-vba-macros/335941-edit-add-delete-listbox-records.html)

[Excel Fox]

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


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




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.ListInd ex, 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

[Tony]

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