Excel 2010 Form Control Combo Box Manipulation

I am trying to achieve something which can easily be achieved with an active X combo box on Excel.  I have a form control combo box, the input range is in column A.  In addition to the values that the drop down box shows I also want the ability to manually put in a value that is not in the input range.  I could do that using active x control combo box but need to eliminate this as some users with Mac are having problems and also ran into other issues with active x control objects on the file.

Is it possible to achieve this using form control combo box?  Is there any way to manipulate this form control via VBA to do that?

Attached is my sample file illustrating what I just described above.
C--Users-rerden-Desktop-FormControl.xlsx
Ray ErdenBusiness Systems AnalystAsked:
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.

Saurabh Singh TeotiaCommented:
trusxlsol,

Unfortunately what you are trying to do you can't since the combobox you are using doesn't support the feature that you are talking about and it has to be linked to a range only..And no you can't modify or tweak this feature through macro/vba as well since it's designed specifically in this manner and you can't tweak around it..

Saurabh...
Martin LissOlder than dirtCommented:
Here's another approach based on this article.
28708602l.xlsx
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You can achieve that by simply adding data validation drop down list instead of adding a form control combobox or is there any specific reason for not using data validation drop down list?

And if you decide to add a data validation drop down list, after mentioning the source range on settings tab, click on Alert Tab and uncheck the "Show error alert after invalid data is entered" box.

This way user can select a value from the drop down list and also can input any value which is not in the drop down list.

For details refer to the attached workbook.
C--Users-rerden-Desktop-FormControl.xlsx

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
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Ray ErdenBusiness Systems AnalystAuthor Commented:
I understand that form control combo box is not supporting the functionality that I am trying to implement.  Already used validation drop down box with error option box checked.  Unchecked that box so no error will pop up if something else is entered other than what the list shows.  Like I said I was able to achieve what I wanted to do with Active X type combo box but some of the users with Mac machines are having problems with that as Mac is not supporting this technology.  In addition to that having Active X control objects on the file was causing problems after using File>Print.  All the objects were being shifted on file although the printout was looking good. I eliminated this issue either via custom print macro or the complete removal of all the active x controls from the file and doing regular File>Print.  Long story short I will stick to validation drop down boxes with error function disabled so user can input a value outside of the items in the list.

Thank you!
Ray ErdenBusiness Systems AnalystAuthor Commented:
Thank you Saurab and Martin.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome trusxlsol!
Martin LissOlder than dirtCommented:
Thank you Saurab and Martin.
You selected Saurabh's and sktneer's soultions. Is that what you meant to do?
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 Excel

From novice to tech pro — start learning today.