Post Excel UserForm data based on Selected ListBox value

[simora]

On my Excel userForm I am populating a combobox from worksheet names at Private Sub UserForm_Initialize and then populating a ListBox with names from the spreadsheet so that some textboxes are filled In like



Private Sub ListBox1_Change()
Set SourceRange = Range(ListBox1.RowSource)
TextBox1 = ListBox1.Value ' Column A
TextBox2 = SourceRange.Offset(ListBox1.ListIndex, 1).Resize(1, 1).Value ' Column B
TextBox3 = SourceRange.Offset(ListBox1.ListIndex, 2).Resize(1, 1).Value ' Column C



The ComboBox selection sets the sheet that I then show the RowSource like this Using Select / Case


Case "Sheet2"
With Sheet2
ListBox1.RowSource = Range(.Range("A4"), .Cells(Rows.Count, "A").End(xlUp)).Address(, , , True)
End With

This all works up to this point. Now the problem!

How do I post the contents of additional textboxes back to the Worksheet based on the name selected in the Listbox.
I want to post the values of TextBox4, - TextBox8 to the worksheet. TextBox4 is Column D, 5 is E etc...etc....
Any assistance, code etc...... apreciated.

Thanks

[lucas]

I must be missing something in your question but it looks like textbox 4 would look like the following based on what you have posted......

TextBox3 = SourceRange.Offset(ListBox1.ListIndex, 3).Resize(1, 1).Value

It would really help if you guys would post an example so we don't have to recreate what you have right there in front of you in order to help you....

[simora]

Lucas:
RE: TextBox3 = SourceRange.etc..etc..Value et al
is just the Userform displaying in Textboxes & Labels values that are already on the worksheet.
I am now trying to post new values from the Textboxes 4 -8 but they will have to be in the same Row# as the selected value in the ListBox which represents Col A values.

Hope this clears things up.

[lucas]

Something like this then:
ListBox1.RowSource.Offset(0, 4).Value = TextBox4.Text
ListBox1.RowSource.Offset(0, 5).Value = TextBox5.Text
ListBox1.RowSource.Offset(0, 6).Value = TextBox6.Text

again, I did not re-create your file and I see no reason to if since you have it and seem unwilling to let anyone see it.

It should be really simple to copy the file and delete the parts that are not relevant to your problem.

You could probably and should construct a simple with statement to make this a little cleaner.

[simora]

Sorry about that. The workbook is over 20 sheets and they are just a mess. I was able to find a link that really helps here: www.vbaexpress.com/kb/default.php?action=13&kb_id=600 (http://www.vbaexpress.com/kb/default.php?action=13&kb_id=600)
The section where it says 'When a name in the listbox is clicked, select the corresponding row is what I really can modify.

I really appreciated your help.

Thanks

[lucas]

I understand, kinda. Glad you found a way to go forward.

[simora]

Lucas:
Even though I figured out a work around, I did try your code and it gave me an Invalid qualifier. Is there an easy way to link a listbox selection to a row number in the range that the Listbox was populated from.