ListFillRange property on a combo box

[Bagsy]

I wonder if anybody can help with this please
I have some data that I have imported into my workbook from an excel workbook using get data then get data from file.

I have a combobox which is linked to a cell, its so I can search the first column of the imported data then select it and it will enter it into the linked cell. The problem is when I open up the template and I click on the arrow on the side of the combobox it just shows blank rather than a dropdown with the list in it, if I start to search in the combobox its fine it just acts like a dropdown box and I can select the data I need and it enters into the linked cell all OK.
I know/think I have to put a range name into the ListFillRange property in the combo box but cannot get it to work, it will not accept my range names.
I have created a new name RangeDataList and referred it to the named range MainData but I cannot get the ListFillRange property of the combobox to accept it.

I actually have two combo boxes on the same sheet but once I know what to do, I can apply the same to the 2nd combo box
I have attached the code for both combo boxes & pics of the properties & name manager boxes

Any help would be appreciated

'sheet's name where the list (for combobox) is located. [in the sample: sheet "data"]
Private Const sList As String = "MainData"
Private Const s2List As String = "Sub_Contractors"

'cell where the list start [in the sample: cell A2 in sheet "Data" ]
Private Const sCell As String = "A2"
Private Const s2Cell As String = "A2"

'the linked cell
Private Const xCell As String = "B15"
Private Const x2Cell As String = "C3"

Private vList

Private Sub ComboBox1_Change()
Dim z, ary

With ComboBox1
    If .Value <> "" And IsError(Application.Match(.Value, vList, 0)) Then
        With Sheets("Main Data")
            ary = Application.Transpose(.Range(sCell, .Cells(.Rows.Count, .Range(sCell).Column).End(xlUp)).Value)
        End With
                
                For Each z In Split(.Value, " ")
                    ary = Filter(ary, z, True, vbTextCompare)
                Next
           .List = ary
           .DropDown
    
    ElseIf Not IsError(Application.Match(.Value, vList, 0)) Then
        Range(xCell) = .Value
    
    Else
        Range(xCell) = .Value
        .List = vList
    End If
End With
End Sub

Private Sub ComboBox1_GotFocus()

With Sheets("Main Data")
vList = Application.Transpose(.Range(sCell, .Cells(.Rows.Count, .Range(sCell).Column).End(xlUp)).Value)
End With

    ComboBox1.MatchEntry = fmMatchEntryNone
    ComboBox1.Value = ""
    'ComboBox1.ListRows = 10 'to show how many item
End Sub


Private Sub ComboBox2_Change()
Dim z, ary

With ComboBox2
    If .Value <> "" And IsError(Application.Match(.Value, v2List, 0)) Then
        With Sheets("Sub Contractors")
            ary = Application.Transpose(.Range(s2Cell, .Cells(.Rows.Count, .Range(s2Cell).Column).End(xlUp)).Value)
        End With
                
                For Each z In Split(.Value, " ")
                    ary = Filter(ary, z, True, vbTextCompare)
                Next
           .List = ary
           .DropDown
    
    ElseIf Not IsError(Application.Match(.Value, vList, 0)) Then
        Range(x2Cell) = .Value
    
    Else
        Range(x2Cell) = .Value
        .List = vList
    End If
End With
End Sub


Private Sub ComboBox2_GotFocus()

With Sheets("Sub Contractors")
vList = Application.Transpose(.Range(s2Cell, .Cells(.Rows.Count, .Range(s2Cell).Column).End(xlUp)).Value)
End With

    ComboBox2.MatchEntry = fmMatchEntryNone
    ComboBox2.Value = ""
    'ComboBox2.ListRows = 10 'to show how many item
End Sub

 

[6StringJazzer]

I have created a new name RangeDataList and referred it to the named range MainData but I cannot get the ListFillRange property of the combobox to accept it.

The screen shot shows that the name is MainDataList, not RangeDataList. Is that the problem?

 

[Bagsy]

Hi 65StringJazzer

Thanks for replying.
I’m not sure if that is the problem, I just thought that you had to create a new named range, I thought you could name a range anything as long as there are no spaces, then in the box just select “refer to” then type in the named range you want it to refer to.
In my case the named range I want it to refer to is called MainData

I will try out your suggestion on Monday.

Many thanks for taking the time to help

 

[6StringJazzer]

My point was that you said you can't get the ListBox to accept RangeDataList for ListFillRange. The reason that it won't accept it is that your range is actually called MainDataList.

 

[Bagsy]

65StringJazzer thanks for getting back to me
I tried just putting the Named Range “MainData” in the “ListFillRange” properties of the combo box and it wouldn’t accept it, so I looked online and read somewhere that I had to create a new named range and then refer it to the named range that you needed
So, I created a new named range and called it “MainDataList” then I referred it to the Named range in my case this was “MainData”.

I then entered the new named range “MainDataList” into the “ListFillRange” in the combo box properties and it still wouldn’t accept it

I know this is sounding complicated, but I cannot get the properties of my combobox to accept these named ranges

“MainData”
“MainDataList” (which I created because I read that you had to create a new name and the refer it to a named range)

Thanks for everything

 

[Akuini]

You don't need to use named range & ListFillRange.
Try this:

Private Sub ComboBox1_GotFocus()

With Sheets("Main Data")
vList = Application.Transpose(.Range(sCell, .Cells(.Rows.Count, .Range(sCell).Column).End(xlUp)).Value)
End With

    ComboBox1.MatchEntry = fmMatchEntryNone
    ComboBox1.Value = ""
    ComboBox1.List = vList
    'ComboBox1.ListRows = 10 'to show how many item
End Sub


Private Sub ComboBox2_GotFocus()

With Sheets("Sub Contractors")
vList = Application.Transpose(.Range(s2Cell, .Cells(.Rows.Count, .Range(s2Cell).Column).End(xlUp)).Value)
End With

    ComboBox2.MatchEntry = fmMatchEntryNone
    ComboBox2.Value = ""
    ComboBox2.List = vList
    'ComboBox2.ListRows = 10 'to show how many item
End Sub

And change v2List to vList in this part:

Private Sub ComboBox2_Change()
Dim z, ary

With ComboBox2
    If .Value <> "" And IsError(Application.Match(.Value, v2List, 0)) Then

If that doesn't work then could you upload your sample workbook to a free site such as dropbox.com & then put the link here?

 

[Bagsy]

Thaks Akuini
Thanks for helping
I will give this a go

 

[Bagsy]

Akuini
That is great works perfectly and so simple.
Thank you so much for taking the time to help me.

Gary

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.