Insert scroll bar and update cells when clicking on item in scroll bar window

Hi Forum!
I wonder if the following is possible.
In Sheet1 in this attachment Fruits.xlsm I have a long list of many different kinds of culinary fruits. In Sheet2 there is the same list but with all the respective information spread out through 5 more columns. With your help I would really like to accomplish a couple tasks.
A) Insert a scroll bar in Sheet1 so the long list will only be visible in a window that has a scroll bar on the side
B) When clicking on an item in the scroll bar window I would like that the grey box in range D3:N5 will update all the information pertaining to the fruit that was clicked on.

I know how to use Vlookup but this case is a bit tricky.

Any help will be greatly appreciated.
Thanks in advance

Hi

If I understand your requirement correctly there are two ways to implement this, either using a form combobox control or an activeX combobox control, see attached. The Axtive X control has the advantage that it allows type ahead I.E. typing the first few characters of the fruit name will start the list at that point however it is a little flaky when changes are made to the source list it does not automatically refresh the listFillRange property of the control so I have added some code to do this in the worksheet_activate and the combo box gotFocus events. The form control also has an issue if the last item in the source list is deleted whilst it is selected so I have added some code to the worksheet_activate event to clear the linked cell.
N.B. the form controls return the index of the selected item not the actual item to the linked cell so the INDEX function has to used to pick up the fruit details. The active x control returns the actual item to the linked cell so the VLOOKUP function can be used. In both cases the liked cell has been hidden behind the control. I have set up dynamic named ranges to provide the names list and the details range, these will automatically adjust when items are added or removed.
It would also be possible to use a listbox control either form or active x which can display multiple rows from the list, up to the full list, with a scrollbar, again see attached. I haven't tested it but they would probably have the same issues discussed above.

Regards
Ian

Wow! I take my hat off for you!
Your attachment is short sweet and simple
I have a small request to ask you, I happen to like the active x control better because it gives me the option of typing in the first few letters, is it possible to display like 20 fruits in the list?
See attachment as to what I mean Fruits.xlsm
Also, is it possible that the scroll button can work like a regular windows scroll button in regards to scrolling with the wheel that is on the mouse and that if I am holding my mouse button down on the scrolling bar and moving the mouse either upwards or downwards that whatever is in the scrolling box will continue to get scrolled through even if I move my mouse away from the scroll box?
Meaning when I scroll through the regular excel window as long as I am holding the mouse button down I can move away from the scrolling bar and onto anywhere in my screen and excel will continue scrolling.

I'm sorry about that, I didn't realize that my co-worker was logged on to my computer with her username
I was answering you with her account.

Hi

Unfortunately I work exclusively from a laptop so don't have a mouse, if I double click and hold on my pad above the scrollbar it will scroll continuously to the top and vice versa. There doesn't appear to be anyway to change the scrolling behaviour of the controls. The workbook you attached to your last post contained a form listbox control not an Active x so I have added one to my original attachment. If you want to see more of less rows in the listbox then from the developer tab click on design mode, you can then select the control and drag it out to display as many rows as you require. Remember to click on design mode again to turn it off.

Regards
Ian

Thank you. You've been a great help.
Hope to see you around

Hi Ian.
I'm setting up my whole system based on your attachment but i'm having trouble trying to create the list box. I was wondering if you can help me out with it.
1) How do I get the scroll bar in the active x list box?
2) How do I set the range in the list box to where it should refer to?
3) I noticed you have a bunch of codes in the workbook. can you explain to me the importance of it?

Please Login or Register  to view this content.

Thank you and hope to hear from you soon.