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
trusxlsolBusiness Systems AnalystAsked:
Who is Participating?
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...
0
Martin LissOlder than dirtCommented:
Here's another approach based on this article.
28708602l.xlsx
0
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
0

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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.