PDA

View Full Version : [SOLVED] Assigning data from a range to a combobox



BexleyManor
08-10-2005, 09:57 AM
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
08-10-2005, 10:08 AM
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
08-10-2005, 10:16 AM
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
08-10-2005, 10:41 AM
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
08-10-2005, 11:05 AM
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 :clap: :bow:

BexleyManor
08-10-2005, 11:52 AM
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
08-10-2005, 01:46 PM
DRJ's code works! It is also not case sensitive.

PS - You need to put the code in SHEET1.

Jacob Hilderbrand
08-10-2005, 01:49 PM
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
08-10-2005, 02:20 PM
Thanks guys, you've been such a great help.

jindon
08-10-2005, 03:57 PM
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