PDA

View Full Version : Solved: Userform ListBox Row selection



simora
07-13-2010, 04:42 AM
I have a UserForm ( SalesForm) that has a ListBox

When I select a Name from the ListBox, it populates some fields on the UserForm.( SalesForm)

This is how I get the Rowsource
Set SourceRange = Range(ListBox1.RowSource)


I have another form ( NSF Form ) that is selected from the SalesForm UserForm

I DO NOT Unload the ( SalesForm)

How can I find out which row is selected in the listbox on the SalesForm UserForm from the NSF Form, so that I can post data to the same row of the person selected on the spreadsheet using the NSF Form to get the data.

I tried using

Set SourceRange = Range(SalesForm.ListBox1.RowSource)

then to post the data, I tried
SourceRange(ListBox1.ListIndex, 11).Offset(1, 0).Value = TextBox5.Value

The Set SourceRange = Range(SalesForm.ListBox1.RowSource) gives an error

Any ideas on how I can and should proceed.

Thanks

p45cal
07-13-2010, 08:55 AM
Set SourceRange = Range(SalesForm.ListBox1.RowSource) should be OK, but to check what's in RowSource put this line in just before the above line:
msgbox SalesForm.ListBox1.RowSource just to check it's a sensible address. I wonder if there's a sheet ref in there mucking things up?

Second, the following line:SourceRange(ListBox1.ListIndex, 11).Offset(1, 0).Value = TextBox5.Value if it's in NSF's code module, is trying to reference the ListBox1 on NSF, and if you haven't got one it will error. If you have got one, but haven't set its ListIndex to the same as SalesForce.ListBox1's then its ListIndex will be -1. This shouldn't cause an error if the SourceRange starts in row 3 of the spreadsheet or below. This is because Sourcerange(-1,11) is 2 cells above the top row of Sourcerange.

So a few things to check.

I wouldn't bother with a ListBox on NSF, instead use:
SourceRange(SalesForce.ListBox1.ListIndex, 11).Offset(1, 0).Value = TextBox5.Value

simora
07-13-2010, 09:13 AM
Thanks p45cal:

There was an issue with sheet ref in there as you suspected.
Now it works as expected thanks to your final comment.