ListFillRange property on a combo box

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
377
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
'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

Capture.PNG


Capture2.PNG
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
807
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 26, 2005
Messages
377
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 27, 2010
Messages
807
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Feb 26, 2005
Messages
377
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Feb 1, 2016
Messages
2,960
Office Version
  1. 365
Platform
  1. Windows
You don't need to use named range & ListFillRange.
Try this:
Rich (BB code):
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:

Rich (BB code):
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

Active Member
Joined
Feb 26, 2005
Messages
377
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thaks Akuini
Thanks for helping
I will give this a go
 

Bagsy

Active Member
Joined
Feb 26, 2005
Messages
377
Office Version
  1. 365
Platform
  1. Windows
Akuini
That is great works perfectly and so simple.
Thank you so much for taking the time to help me.

Gary
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,960
Office Version
  1. 365
Platform
  1. Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top