Copying an Excel 2010 Combo Box

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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.

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

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.

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.

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks Martin. I am reviewing the link now.

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.

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

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.

wthrottle, did my solution help you?