Link to home
Start Free TrialLog in
Avatar of kgerb
kgerbFlag for United States of America

asked on

Alternative to embedded ActiveX Listbox

Hello everyone,
I'm pretty fed up with embedded ActiveX controls on my worksheets.  I'm so sick of them getting all messed up whenever I dock/un-dock my laptop and my display resolution changes.  I have tried Dave Millers Add-in for saving the location of the controls and it works but I don't like having to do that every time.

Currently I am in the process of making up a dashboard and I need a multi-column control, either a combo box or a list box.  I REALLY don't want to use an ActiveX control.  Here are some options I've thought of...

  1. Using a Form control and try to fake the multi-column effect with spaces or tabs between entries.  This works if I use a fixed width font but I don't want to do that.  If I use a variable width font I'm stuck trying to figure out the length of the text string in points and translating that into an equivalent number of spaces.  Pretty ugly.
  2. Employing a user form.  ActiveX controls do not get messed up when used in a form like they do when embedded on the worksheet.  I was even thinking of trying to embed the user from onto the worksheet as a container for the controls.  Seems backwards as the worksheet itself is a "container" for the controls, it just sucks.
  3. Somehow using data validation drop downs.  Not really sure how this could give me the multi-column effect but I suppose it is an option.

Anyone else have any ideas?  Sharing your thoughts would be greatly appreciated.

Thanks,
Kyle
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I've attached a workbook from this article that I believe does exactly what you want.
example-data-table-with-scroll-bar.xlsm
Avatar of kgerb

ASKER

Hello Martin,
Thanks for the article and the example workbook.  I'm scratching my head as to exactly how it works but it looks very promising.  Thanks again very much for your input.
One of the key things is the formula in B9 on the Data sheet.

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
Sorry, I should have said B3.