Hello,
I have a ListBox with four values in it; Place A, Place B, Place C, Place D.
When I hit a CommandButton, I want Place B to be selected.
In reality, the list is much longer and positioning varies -- I need to do it by name, not by index.
Any ideas?
are you filling the listbox from rowsource?
if yes
then in your command button use match function to find the index -1 (-1 because listindex starts at 0)
assuming rowsource is A1:A4
Please Login or Register to view this content.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
humdingaling,
The ListBox is populated by a PivotTable on another Sheet upon Workbook Open. It is a list of "Places" exported from our Accounting software. Essentially, the button is a "Favorites" button for the user to select certain values in the ListBox.
Unfortunately, I cannot "select" the value by Index, because it is very likely that the Index will change. I need to "select" it by its name. Is that possible?
Is there a variation of the code above that will find out the Index from the PivotTable list?
Enigma, looking at the code offered, it appears he is using a Lookup function to find the dynamic position of the "Place B" in the current list. Read it carefully and see if you concur. Based on your comments this would be the correct approach to take.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
It is not populated by RowSource, it is populated by a PivotTable.
So change the Range to the column where the Pivottable lists those values. If it were in a table on sheet "MyTable" and the values started at P2 and went down, then something like:
These are examples meant to give you the understanding to create the actual usable reference yourself.Please Login or Register to view this content.
still not sure how you are populating your listbox though
the data comes from pivot table but how are you getting it into your list box??
there seems to be an issue with Space in match formula
use wildcards instead of space
see attached example code in action populating the listbox via pivot table....done by row sourcePlease Login or Register to view this content.
Last edited by humdingaling; 08-13-2014 at 10:28 PM.
See Below.
Last edited by EnigmaMatter; 08-14-2014 at 09:12 AM. Reason: Replaced With More Clear Post
I don't know if I am being clear as to what the Spreadsheet houses.
On Sheet1 is a PivotTable. The PivotTable has a Filter called "Customer Name", which allows the user to select a Customer and view information.
On Sheet2 is a ListBox. The ListBox is populated from the aforementioned filter "Customer Name" with the following code:
On Sheet2, I also have a CommandButton that acts as a "Favorites" option for selected users. When the user hits the CommandButton, I want only certain Customers to be selected. Normally, I would be able to say something along the lines of:Please Login or Register to view this content.
HOWEVER, because the list changes order and names frequently...I cannot select by index. I need to select the values by name.Please Login or Register to view this content.
Is this even possible? As a workaround, I have just made the button clear the ListBox, and only populate the ListBox with the names of Customers I desire by using:
Please Login or Register to view this content.
Please Login or Register to view this content.
Andy,
That looks like it makes perfect sense.
However, I get Run-Time Error 380, Invalid Property Value.
I get 380 error if the value being set is not in the listbox.
Andy,
The value is DEFINITELY set to a value that is in the ListBox; I tried multiple values, and even attempted adding a value for testing.
To no avail.
It is a multi-select ListBox; that could be why.
That would be the reason.
You could loop through the listbox if the suggested Match function does not work.
Perhaps you could use Match with the contents of the listbox?
Please Login or Register to view this content.
If posting code please use code tags, see here.
@EnigmaMatter I thought you wanted to limit the list to a sub-set of favourites. At least thats how I interpret your earlier post #9
That doesn't gel with your attempt to select one item with the button. Why do you need to do that if your trying to generate a favourites subset?
Sent from my iPad using Tapatalk
CoolBlue,
I am trying to select the subset of favorites from the larger list, not just one. But I figured that I could determine the rest of the code if someone gave me that.
Norie,
That code looks neat. However, it says type-match error.
Where does it say that?
How have you declared Res?
I declared Res as a Variant.
Res = Application.Match("Place B", Application.Transpose(ListBox1.List), 0) is where it stops.
It works just fine for me, where did you put the code?
I put it inside of a CommandButton.
Can you upload an example workbook?
Click on GO ADVANCED and use the paperclip icon to open the upload window.
Okay, attached.
This works with the workbook you attached.
The reason the code I posted before doesn't work is because, for some reason, the listbox has 3 columns though you are only populating 1 and the column count is 1.Please Login or Register to view this content.
Modification to Norie's code to populate the listbox and then use the Match method.
Thisworkbook code
Please Login or Register to view this content.
I had the same idea as Andy to use the pivot table object model, but he was quicker than me!
Although I did add a sort to further exploit the pivot table object.
Here is another way that still uses Norie's clever idea but tolerates the extra columns...
Please Login or Register to view this content.This solution also exploits the fact that the ListBox.List object is a variant array.Please Login or Register to view this content.
Last edited by coolblue; 08-15-2014 at 08:43 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks