Trying to populate combobox (on a worksheet) w/ a named range... getting error '380' Could not set the ListIndex Property

[kbishop94]

Hello

I have a named range on a worksheet named "LookupLists"

Actually, there are 2 named ranges that point to the data that I am trying to capture within the combobox: (btw I found this formula on another thread here on this board... I used this particular code because my data within the named range will change frequently and this was what I was directed when I did a search on here... if there is a better/easier way of doing what I am trying to do, then please feel free to make any suggestion. )

The first one I have named "Size"
it refers to:
=MATCH(9.99999999999999E+307,LookupLists!$T:$T)-ROW(LookupLists!$T$1)+1

the second on I have named "Data"
it refers to:
=OFFSET(LookupLists!$T$1,0,0,Size)

On my main worksheet, I have a combobox named "cboName"

I have the ListFillRange property blank

Here is the code I have on the worksheet where I have the combobox "cboName" (which is on a different worksheet where the named ranges are located)

Sub WorkSheet_Activate()
Worksheets("Seatex Incident Log").OLEObjects("cboName").ListFillRange = "Data"
cboName.ListIndex = 0
End Sub

Here is the complete property settings for my combobox "cboName"

Thanks for any help and/or suggestions

 

[My Aswer Is This]

Not sure why you think a named range is required but.
And not sure why you think you need all this much code to just load a worksheet Combobox with value.

Here is a example of how I do it

Or is your named range a Excel Table
Now my comboboxes have my name.
Modify Combobox name and sheet name and range name

Private Sub CommandButton1_Click()
'Modified  1/6/2020  10:05:18 PM  EST
ComboBox1.Clear
ComboBox1.List = Sheets(2).Range("New").Value
End Sub

 

[My Aswer Is This]

Here is a example of how to load values into a ComboBox
This example shows 3 different named ranges on two different sheets

Private Sub CommandButton1_Click()
'Modified  1/7/2020  2:00:50 AM  EST
Dim r As Range
ComboBox1.Clear
For Each r In Sheets(1).Range("Alpha")
ComboBox1.AddItem r.Value
Next
For Each r In Sheets(1).Range("Bravo")
ComboBox1.AddItem r.Value
Next
For Each r In Sheets(1).Range("Charlie")
ComboBox1.AddItem r.Value
Next
For Each r In Sheets(2).Range("Delta")
ComboBox1.AddItem r.Value
Next
End Sub

 

[Norie]

Is the combobox actually being populate at all?

What happens if you remove this line of code?

cboName.ListIndex = 0

Have you checked what value the named range Size is actually returning?

You should be able to do that with this simple formula.

=Size

 

[kbishop94]

Hello and thanks to all that replied for the replies, questions & suggestions

Not sure why you think a named range is required but.
And not sure why you think you need all this much code to just load a worksheet Combobox with value.

"My Aswer Is This": Thank you for your help with the code you supplied.
To answer your question, my short answer is this:

"it’s for simplification and consistency that come with using the same named range for an entry regardless of whether the combobox is on a userform, or on a spreadsheet within my workbook"

If you would like a better, more detailed explanation, please continue reading below where I provide a description of my workbook including its function as well as how the parts of it that are applicable to my original request work.


I didn't want to get into too much background on my workbook and its code if for nothing else than to just keep it simple with my request and not drown the reader with information over-load... but I see now I should of provided at least some information as to why I am attempting to do exactly what it is that I am asking:

So the workbook I am working on is one that has been growing (both in size in regard to the number of entries as well as complexity with the number of forms and code within it) for several years now.

When I first created it about 3 years ago, the main worksheet (example shown below) contains within the first 16 rows a "summary area" that when the workbook is opened it calculates various metrics based on the previously entered data (from row 18 down to the last row, which is now sitting at 699.) The data that is entered and added to a new row each time a user adds an entry is a "quality incident" and that entry must be added via a userform. One form is for entries, and another userform is for revising a previously entered incident.

The main worksheet page looks like this:
<pic of main worksheet>

The middle section (right below where is says: "SUMMARY OF EVENTS" in columns I & J) of the above pictured spreadsheet contains 4 comboboxes (these are the ones I am asking my question about) that will FILTER that particular column (example, the combobox for “CUSTOMER NAME” will filter column G which is the “Customer” column.)
All the way over to the left you will see a bright green command button labeled "ID#". Each column has a button like this that will SORT everything based on the values in that column. So the user can either filter the data from either of the 4 comboboxes, and/or then sort the data by clicking on any of the command buttons for each column. When a command button is selected, it sorts the data AND turns that particular button GREEN so that the user can see which column is currently being sorted. This is the setup/design I came up with 3 years ago when I created this.


I have a separate worksheet just for named ranges that are accessed via (multiple) userforms... which, btw, all function just fine. I am now getting to the reason for my original question (yes, finally )... Although I have no problems with my existing code as it pertains when using the named ranges WITH USERFORMS, I cannot figure out how to do the same with comboboxes on a worksheet.
<pic of named ranges worksheet>

The comboboxes on my WORKSHEET have always been populated when the workbook is opened (on open event) and using the .AddItem function.
<pic of on open event code>

Using the ".Additem" method works well enough, BUT... In several cases I have the same function for a combobox (for selecting an employee 'Name', for example) on userforms (where the data for populating those boxes are retrieved from named ranges) and on a spreadsheet (where they are populated using the “.AddItem” as described above.)
My named ranges that are on a separate worksheet are all dynamic ranges because whenever a user goes to select a choice from a combobox, and it’s not there (choices are ‘MatchRequired’), they then have the option of selecting a command button that will then trigger a pop-up form where they can then add the name they need and at which time it will then be inserted into the specific named dynamic range for that combobox (sure hope that all makes sense…)

<pic of userform for entering a new incident>

So now we have arrived (finally, I know ) at the reason why I want to keep my comboboxes that are on the WORKSHEET referring to the same named range that the comboboxes on my userforms refer to: it’s for the simplification and consistency that come with using the same named range for an entry regardless of whether the combobox is on a userform, or is on a spreadsheet within my workbook.

I'm sure this is something really simple that I am missing, but I also hope that by me providing a description of how my workbook functions and its purpose, maybe some of you folks will have a suggestion for handling this in another, more efficient or simpler way. Everything I know about VBA and excel I learned from this site and from the much more advanced and smarter experts folks than I am .

As always, thanks again for everyone's help and guidance. It is very much appreciated.

 

[kbishop94]

Is the combobox actually being populate at all?

No its not (either with "My Awser Is This" supplied code, or any other way I have tried.)

What happens if you remove this line of code?

cboName.ListIndex = 0

No difference; the combobox is still not populated and is empty.

Have you checked what value the named range Size is actually returning?

You should be able to do that with this simple formula.

=Size

I will try that! great suggestion.

 

[kbishop94]

Have you checked what value the named range Size is actually returning?

You should be able to do that with this simple formula.

=Size

I get: #N/A
I get the same thing for either Size and for Data

 

[My Aswer Is This]

Thanks for your explanation
Everyone has their own way of doing things.
My script was for if you had more then one named range you want added to a ComboBox
And this same script should work if on a Userform or a sheet.
I see you said in previous post that my script did not work.
Ideally I would not use a named range because a named range unless part of a Excel Table is not dynamic.

And like your script of adding names where you used a additem approach
I would use a dynamic range so if you want to add a name you do not have to modify the script.

Like this:

Private Sub CommandButton1_Click()
'Modified  1/7/2020  11:37:05 AM  EST
Dim lastrow As Long
lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.Clear
ComboBox1.List = Sheets(1).Range("A1:A" & lastrow).Value
End Sub

 

[My Aswer Is This]

Not sure why you said in previous post my script did not work.
Obviously the named ranges needed to be changed and the ComboBox name maybe needed to be changed.

 

[kbishop94]

Not sure why you said in previous post my script did not work.
Obviously the named ranges needed to be changed and the ComboBox name maybe needed to be changed.

I dont know why it didn't work... but its probably because I have something wrong or something I failed to tell you... but here is what I have so you can see exactly what your're working with:

Here is the worksheet "LookupLists" with the 2 named ranges ("Data" and "Size" ) shown in the Name Manger window:

And here is the combobox "cboName" along with the properties box on the main worksheet (which is named "Seatex Incident Log")

And here is the VBA code that is on the worksheet "Seatex Incident Log"

Private Sub cboName_Click()
cboName.Clear
cboName.List = Sheets("LookupLists").Range("Size").Value
End Sub

Now, if I change the last line to...

cboName.List = Sheets("LookupLists").Range("Names").Value

it still doesn't work(?) "Names" is the existing dynamic named range on the same worksheet (LookupLists) that I use for the previously mentioned (in my post above) userforms (with no issues)
Did I miss something? Any idea why its not working for me?

Thanks so much.