Copying an Excel 2010 Combo Box

I created a combo box (Active X) in cell A1.  How do I copy it down to A2 through A500 to create a form for users to input data?

On a side note:
After I have selected the value from the combo box drop down and then press the TAB key on the keyboard the active cell does not move to the next cell to the right.  I have to click the cell with the mouse.  How can I enable the TAB behavior?

Thank you.
wthrottleAsked:
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.

Steven HarrisPresidentCommented:
Instead of a combobox you can use Data Validation. If you click on the cell you want it in then go to Data --> Validation and where it says "Allow" click list. Set your source for the list and you're done. Now there is a built in drop down list in the cell, and you can refer to the cell directly using the formula "=A1."
0
wthrottleAuthor Commented:
Thanks for the quick reply ThinkSpace!  I like the Data Validation.  I lost a little functionality as compared to the active X combo box - As I begin to type text such as "Sa... (looking up from a customer list) the active highlighting does not jump down the list.  Do you know how to make that functionality work using data validation?
0
Martin LissOlder than dirtCommented:
It can be done but it involves floating an AcxtiveX combobox over the cell. I do it extensively in a project of mine and here's the article that provided the original code.
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Steven HarrisPresidentCommented:
You would have to run a macro for AutoComplete, or head back to Combo Boxes.

Of course, this would need some editing:

Sub MyValidation(ByVal KeyCode As Long) 
    Dim strChr As String 
    If Not TypeOf Selection Is Range Then Exit Sub 
     
    strChr = Chr(KeyCode) 
     
    Dim a As Variant 
    Dim foundone As Boolean 
    Dim top As Long, bottom As Long 
     
    If ActiveCell.Address = "$C$2" Then 'Make sure validation can only be changed in cells I want.
        ActiveCell.Value = "" 'Remove any previous value
        foundone = False 'Variable that tells whether a top has been found
        a = [Engineers].Value 'Sets to entire list
        For i = LBound(a) To UBound(a) 'Loop through list range
            If InStr(1, a(i, 1), strChr, vbTextCompare) = 1 And a(i, 1) <> "Employee Name" And foundone = False Then 
                foundone = True 'found top of list
                top = i 'set top of list
            ElseIf InStr(1, a(i, 1), strChr, vbTextCompare) <> 1 And foundone = True Then 
                bottom = i - 1 'set bottom of list
                foundone = False 'turns off, list must be sorted
            End If 
        Next 
        ActiveWorkbook.Names.Add Name:="NameList", RefersToR1C1:="='Engineering List'!R" & top & "C2:R" & bottom & "C2" 'Define named range NameList with top and bottom, cell for validation should have =NameList as validation formula
    ElseIf ActiveCell.Column = 2 And ActiveCell.Row >= 9 And ActiveCell.Row <= 17 Then 'All works the same except defines different range and uses different input values
        ActiveCell.Value = "" 
        foundone = False 
        a = [Projects].Value 
        For i = LBound(a) To UBound(a) 
            If InStr(1, a(i, 1), strChr, vbTextCompare) = 1 And a(i, 1) <> "Project Name" And foundone = False Then 
                foundone = True 
                top = i 
            ElseIf InStr(1, a(i, 1), strChr, vbTextCompare) <> 1 And foundone = True Then 
                bottom = i - 1 
                foundone = False 
            End If 
        Next 
        ActiveWorkbook.Names.Add Name:="Projects" & ActiveCell.Row - 8, RefersToR1C1:="='Project List'!R" & top & "C1:R" & bottom & "C1" '9 different projects list, projects1 - projects9, assigned to B9:B17 respectively
    End If 
End Sub 

Open in new window

0
wthrottleAuthor Commented:
Thanks Martin and ThinkSpace.  If I went back to Comboboxes is it possible to copy them all at once for the 500 rows of my worksheet?  Or do they have to be built one-by-one?

On the flip side the Data Validation sure is a simple approach.  I will look at the code ThinkSpace posted and see if I can figure out how to apply the variable of my worksheet to it and make it work for me.
0
Martin LissOlder than dirtCommented:
I don't understand what you mean when you say
If I went back to Comboboxes is it possible to copy them all at once for the 500 rows of my worksheet?
0
wthrottleAuthor Commented:
Martin,  I may be missing something simple, but after I create my first combo box it "floats" above the cell and I cannot figure out how to copy the combo box downward from A2 all the way down to row A500.
0
Martin LissOlder than dirtCommented:
If you use the code properly the box will automatically appear over the next one when you go to that cell. In other words there's just one but it moves around.
0
Martin LissOlder than dirtCommented:
BTW in the article I pointed to there's a sample workbook that you can download.
0
Steven HarrisPresidentCommented:
Something I didn't think about...

You created your ComboBox on top of a cell, so ignore the ComboBox for a minute, it should be anchored to the cell.  Highlight the cell and drag down as you would normally.
0
Martin LissOlder than dirtCommented:
You created your ComboBox on top of a cell, so ignore the ComboBox for a minute, it should be anchored to the cell.
In the article it's an ActiveX combo box and so it can be created anywhere and moved anywhere. The Data Validation combobox stays where it is, it just gets covered by the floating combobox and that combobox uses the data from the data validation drop down.
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
wthrottleAuthor Commented:
Thanks Martin.  I am reviewing the link now.
0
Steven HarrisPresidentCommented:
Martin, give it a try.  Create a new ActiveX ComboBox in cell A1.  Select the cell and fill down as if you were copying down a number or formula.
1
Martin LissOlder than dirtCommented:
I'm not sure what the point of that would be. In the article's code there is just one ActiveX combobox.
0
Steven HarrisPresidentCommented:
I am not referencing the article, I am referencing the OP's question of copying it down to other cells.  This eliminates the use of VBA and any modification from what he already has in place.
0
Martin LissOlder than dirtCommented:
wthrottle, did my solution help you?
0
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

Marty - MVP 2009 to 2013
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.