-
Solved: Userform ListBox Row selection
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
-
[vba]Set SourceRange = Range(SalesForm.ListBox1.RowSource)[/vba] should be OK, but to check what's in RowSource put this line in just before the above line:
[vba]msgbox SalesForm.ListBox1.RowSource[/vba] 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:[vba]SourceRange(ListBox1.ListIndex, 11).Offset(1, 0).Value = TextBox5.Value [/vba]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:
[vba]SourceRange(SalesForce.ListBox1.ListIndex, 11).Offset(1, 0).Value = TextBox5.Value[/vba]
p45cal
Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.
-
Thanks p45cal:
There was an issue with sheet ref in there as you suspected.
Now it works as expected thanks to your final comment.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules