Assigning data from a range to a combobox

[BexleyManor]

Midweek hair pulling special!! (Easy one I'm sure for those in the know!)

I'm looking to fill a combobox with data from a range

But...

(please check the attached workbook to see layout)

if I was to enter A in cell A2 then the combobox would populate with the data from range E2:E4. Again, If I entered B in cell A2 then it would populate with range F2:F4 and so on. Ideally It would range from A to L and the corresponding ranges.

Can any of you kind folks give me some pointers??

[Jacob Hilderbrand]

Like this?

Option Explicit
Option Compare Text
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim FillRange       As Range
Dim Cel             As Range
If Not Intersect(Target, Range("A2")) Is Nothing Then
        Me.Combo.Clear
        Select Case Target.Text
            Case Is = "A"
                Set FillRange = Range("E2:E" & Range("E65536").End(xlUp).Row)
            Case Is = "B"
                Set FillRange = Range("F2:F" & Range("F65536").End(xlUp).Row)
            Case Is = "C"
                Set FillRange = Range("G2:G" & Range("G65536").End(xlUp).Row)
            Case Is = "D"
                Set FillRange = Range("H2:H" & Range("H65536").End(xlUp).Row)
        End Select
    End If
    If Not FillRange Is Nothing Then
        For Each Cel In FillRange
            Me.Combo.AddItem Cel.Text
        Next
    End If
Set Cel = Nothing
    Set FillRange = Nothing
End Sub

[BexleyManor]

Hi DRJ,

WOW, not sure!!?? I think your code might be doing too much!! I'll have a go at getting my head round it then report back.

Thanks my friend

**Update**

Just tried it and it doesn't seem to work. If you type A in cell A2 in the attached workbook then I would hope to see the combo filled with Monkey, Rat, Dog. If I typed B it would fill with Cat, Bird, Fish and so on.

[jindon]

Hi
try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim txt, i, rng As Range, col
txt = Array("A", "B", "C"): col = Array("E", "F", "G")
If Target.Range("a1").Address <> "$A$2" Then Exit Sub
    For i = LBound(txt) To UBound(txt)
        If Target.Value = txt(i) Then
            Set rng = Range(Cells(2, col(i)), Cells(65536, col(i)).End(xlUp))
            Exit For
        End If
    Next
If Not rng Is Nothing Then
    With Me.Combo
        .Clear
        .List = rng.Value
    End With
End If
End Sub

[BexleyManor]

Hi J,

Thanks for the suggestion, unfortunately it doesn't appear to work either!!

Does it work for you in the attached workbook??

**Update**

Most unusual, I quit Excel started it back up tried the code again and it worked!!

I suspect the same could be said of Jake's code, I'm gonna try that too!!

Many thanks guys, you've been a really great help

[BexleyManor]

Just as an after thought, is there a way of making the input in A2 case insensitive as currently you are required to type A, B etc so it would be nice to get it to work with a, b etc

Thanks again, this has been soooooo helpful

[JKwan]

DRJ's code works! It is also not case sensitive.

PS - You need to put the code in SHEET1.

[Jacob Hilderbrand]

Yeah the code should work and it not case sensitive. If you put the code in the wrong spot you should get a compile error. Just right click on the sheet tab and select view code and paste in there.

[BexleyManor]

Thanks guys, you've been such a great help.

[jindon]

Just as an after thought, is there a way of making the input in A2 case insensitive as currently you are required to type A, B etc so it would be nice to get it to work with a, b etc

Hi,

Just add following one line on the top the code makes case insensitive

Option Compare Text