Control Listbox & Equation irregularity
Control Listbox & Equation irregularity
(OP)
I have a control listbox in my XL spreadsheet. Its ListFillRange is D32:D55, and its LinkedCell is L6. The BoundColumn value is set to 0. In cell D6, I have a formula that retrieves the value which corresponds to the row that is selected in the listbox: =INDIRECT("F"&32+L6). This takes the index value returned from the listbox in L6 and retrieves the value of the same row. This is well and good. However, whenever I close out this sheet and reopnen it, the value of the last item picked is cleared out. This is due to the fact that the BoundColumn's value is set to 0. When it is set to 1, it shows the last value picked; however, it also returns the value and not the index number, which screws up the aforementioned formula.
That being said, I could change my formula in D6 to:
=VLOOKUP(L6,D32:F55,3).
But when I do, it always resolved the second argument of the array as 1 and not the correct value of the listbox, giving the addres as 1,3 of the array instead of any other index level of the listbox. Has anyone else encountered this problem, and if so, what is the solution to make this work? BTW, I have already tried using INDEX/MATCH in lieu of this formula, which ends in the same result.
On the other side, I could keep my original configuration if I could get the right VBA code to display the last selection in the listbox, placing the code in the ThisWorkbook, Auto_Open routine. I know it has something to do with Listbox.Text, but I can't seem to get past the errors that come up that way either.
I know this is a long posting, of which I apologize, but this is a convoluted mess and I can't seem to figure it out by myself. Your help will be appreciated to achieve
Q'Apla! (Success in Klingon)
That being said, I could change my formula in D6 to:
=VLOOKUP(L6,D32:F55,3).
But when I do, it always resolved the second argument of the array as 1 and not the correct value of the listbox, giving the addres as 1,3 of the array instead of any other index level of the listbox. Has anyone else encountered this problem, and if so, what is the solution to make this work? BTW, I have already tried using INDEX/MATCH in lieu of this formula, which ends in the same result.
On the other side, I could keep my original configuration if I could get the right VBA code to display the last selection in the listbox, placing the code in the ThisWorkbook, Auto_Open routine. I know it has something to do with Listbox.Text, but I can't seem to get past the errors that come up that way either.
I know this is a long posting, of which I apologize, but this is a convoluted mess and I can't seem to figure it out by myself. Your help will be appreciated to achieve
Q'Apla! (Success in Klingon)





RE: Control Listbox & Equation irregularity
= VLOOKUP(L6, D32:F55, 3, FALSE)
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Control Listbox & Equation irregularity
RE: Control Listbox & Equation irregularity
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Control Listbox & Equation irregularity
RE: Control Listbox & Equation irregularity
=IF(L6="~","~",VLOOKUP(VALUE(L6),D32:D55,3,FALSE))
Which tells it to return the tilde if that is what L6 has, or do to lookup otherwise. Thanks for helping!
RE: Control Listbox & Equation irregularity
Cheers,
Joerd
Please see FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.
RE: Control Listbox & Equation irregularity
if you make the ListFillRange is D32:F55 and set the BoundColumn value to 3 and ColumnCount to 3, a trivial bit of VBA code gets your answer as follows:
CODE
Me.Cells(1, 2) = ListBox1.Value
End Sub