Avatar of wthrottle
wthrottle asked on

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.
Microsoft Excel

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
SOLUTION
Steven Harris

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
wthrottle

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?
SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Steven Harris

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

ASKER
wthrottle

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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Martin Liss

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?
ASKER
wthrottle

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.
Martin Liss

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

BTW in the article I pointed to there's a sample workbook that you can download.
Steven Harris

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.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
wthrottle

Thanks Martin.  I am reviewing the link now.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Steven Harris

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.
Martin Liss

I'm not sure what the point of that would be. In the article's code there is just one ActiveX combobox.
Steven Harris

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Martin Liss

wthrottle, did my solution help you?
Martin Liss

I'm glad I was able to help.

Marty - MVP 2009 to 2013