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
LVL 12
kgerbChief EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please see my my Floating ActiveX control article. When using it you will not have the ActiveX problems you described. The one possible downside for you is that the controls are not visible until you select a cell. A couple of test projects that use comboboxes are attached to the article.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I've attached a workbook from this article that I believe does exactly what you want.
example-data-table-with-scroll-bar.xlsm
kgerbChief EngineerAuthor Commented:
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.
Martin LissOlder than dirtCommented:
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
Martin LissOlder than dirtCommented:
Sorry, I should have said B3.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.