How to populate listbox with textbox?

Multi-select listbox in worksheet

• QUESTION: I have successfully used multiselect Listboxes in Userfowms with the 1-MultiSelect option that provides checkboxes in the ListBox for the user to select several items from the list. I would like to use this same feature in a worksheet. I added the ListBox to the worksheet from the Forms toolbar and can populate it using Format Control Input Range. I can't figure out how to add the checkboxes in the worksheet ListBox like I did in the Userform ListBox. I also am unsure on how to use VBA to identify which items were checked. As always, your help is much appreciated. ANSWER: Steve, I sounds like you are using2003 or earlier rather than2007 or later. To use the same listboxes as you did in the Userform, you need to use the Listbox from the Control Toolbox Toolbar - not the one from the Forms Toolbar. These are the ActiveX controls. You set the source range with the Listfillrange property when your on the worksheet (rather than rowsource used in a userform). You will have the same multiselect option. selecting multiselect alone doesn't put the checkboxes in front of the list items. You need to set the ListStyle to 1 - fmListStyleOption. But this should be true as well when used in a userform. Hope that answers the question. ---------- FOLLOW-UP ---------- QUESTION: I was able to add and program the ActiveX Listbox on the worksheet but I am having trouble with the ListBox scrolling feature. If there are 6 entries in the ListBox and I size the Listbox to display less than 6 entries (example: large enough to display 3 entries) then the scroll arrows on the right of the listbox will only allow me to scroll down to entry 5. If I size it to display 6 or more I can see all six entries so I know that the Listbox contains all 6 entries. I know when populating the Listbox the first entry is ListBoxName.List(0) and the last entry is ListBoxName.List(5). Any suggestions on how to scroll to the last item?

• Answer:

  Steve, How the list elements are numbered has nothing to do with how they are displayed (zero base or 1 based). If you do Listbox.Listcount you should get 6. I could not reproduce your problem - but it is possible you have the integralheight property set to false. Here is an explanation of Integralheight (from the help file): The IntegralHeight property relates to the height of the list, just as the AutoSize property relates to the width of the list. If IntegralHeight is True, the list box automatically resizes when necessary to show full rows. If False, the list remains a fixed size; if items are taller than the available space in the list, the entire item is not shown. If it is set to True already, then you might try changine your font height or name settings and see if you can get a combination that works well. As far as I know, there is no systemic problem with displaying the list using the vertical scrollbar in Excel.