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]

I assume the problem is with those named ranges

I just select a range and name it.
But your named range is a lot more then that
But it's always hard to deal with scripts like this if you do not know what it means
But if you like it that is all that counts.

I know there are a lot of real complicated things that work but I sure do not know what this means:
=MATCH(9.99999999999999E+307,LookupLists!$T:$T)-ROW(LookupLists!$T$1)+

That why I just use something like this to load my ComboBox
This allows for more values to be added without using a complicated named range

This here says look in column A for the values to be put in the combobox
If you add more values to column A the script takes that into effect.

Dim Lastrow as long
Lastrow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.List = Sheets(1).Range("A1:A" & lastrow).Value

 

[My Aswer Is This]

I just had to go back and read the original question
And I'm not sure what the question is.
You later on mentioned you have had this script for several years so I'm not sure what the question is.
You said:

When I first created it about 3 years ago,

So can you tell me what is the question. Are you now after 3 years having a problem?​

 

[kbishop94]

I just had to go back and read the original question
And I'm not sure what the question is.
You later on mentioned you have had this script for several years so I'm not sure what the question is.
You said:
When I first created it about 3 years ago,

So can you tell me what is the question. Are you now after 3 years having a problem?

I re-read it too and it wasn't very clear. I do apologize.

So I was attempting to address your first statement in your first reply as to "why" I would need to have a 'named range' for this.

What I wanted to convey was, in my journey with this workbook (starting 3 yrs ago with conception to where it is now), I did some things differently; and probably not the best or most efficient way either for that matter back in the beginning compared to how I have done things more recently.

One of those things (way back then) was how I utilized the ".AddItem" method for populating the comboboxes that are on the worksheet. I didn't even have the userforms back then. People just entered data right onto the spreadsheet,... which, was an utter disaster. Data was all getting entered differently (such as spelling errors or using a wrong name) and because of that the data wasn't very useful for using it as it ideally should be used; i.e., identifying trends, areas where repeat incidents or non-conformances were occurring and specific people that were responsible for repeat or multiple incidents.

So over time, I created a dedicated worksheet (LookupLists) where all of the dynamic named ranges are and where all the comboboxes on my userforms are directed to.

These lists will grow and change as different users are adding new entries to them (with pop-up forms they can open and add an item to a list when its not there.)

However, the comboboxes that I started with (way back then) on the worksheet still refer to the '.AddItem' method on the 'This Workbook' code with an 'On Open' event.

The problem is, these (the '.AddItem' lists that are on the 'This Workbook' code) do not get updated with the newer data that gets added overtime via users adding things to the dynamic named ranges on the dedicated worksheet ('LookupLists') when using the 'pop-up forms'.

Since it ('it' = comboboxes on the WORKSHEET that are populated by the '.AddItem' way) was something that was really minor in the scope of everything that I would be working on at any given time, I never really fixed it the way that I needed to... and the few times that I did try to fix it, it never worked and I got errors (which is when I realized that the way a userform populates a combobox from a dynamic named range must be different than the way that its done on a worksheet. And That's it. That's the problem I am trying to fix.

I want the comboboxes on the main worksheet to show the same choices as the comboboxes on the userform.

Additionally, those are areas where I really need to learn and better understand (comboboxes and dynamic named ranges), because I am starting another big workbook that will be utilized by many entities within my organization and will rely heavily on those functions (as well as dependent comboboxes.)

Sorry (again) for the lengthy explanation as well as for all the confusion. Thanks so much for all your help.

 

[Norie]

The formula you are using for the named range Size is for finding the last numeric value in a column, as far as I can see the data in column T on LookupLists is text.

Try using this formula for Size instead:

=MATCH(REPT("z",255),LookupLists!T:T)

 

[My Aswer Is This]

Ok I understand most if not all of what you want.
So I always want to work on projects one step at a time.
I suggest you build yourself a simple little Userform to see how my suggestion would work.

So for my test to work lets work with my control names

So lets start with the Userform

Put on your Userform a ComboBox named ComboBox1

This is default name for your first ComboBox

Now I have a script here that will run automatically every time you open your Userform

Now you will need a worksheet on the active workbook name Input

You can always rename these sheet names later if you want.

Now put this code in your Userform
This code automatically runs when you open the userform
And this code will load all the values you have on sheet named Input in column A
And this code will always load all the values in column A even if later you add more values to the range

Private Sub UserForm_Initialize()
'Modified  1/8/2020  12:30:40 AM  EST
Dim Lastrow As Long
Lastrow = Sheets("Input").Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.Clear
ComboBox1.List = Sheets("Input").Range("A1:A" & Lastrow).Value
End Sub

You can also use this same exact code if you have a ComboBox on a worksheet
On a Worksheet put this code in a button or on a Workbook open event what ever you want

On the worksheet it would look like this:

Dim Lastrow As Long
Lastrow = Sheets("Input").Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.Clear
ComboBox1.List = Sheets("Input").Range("A1:A" & Lastrow).Value

See the code is exactly the same for userform Combobox or sheet Activex ComboBox

 

[My Aswer Is This]

Now if you get that working and you want a script where user can add values to the list on sheet named input. We can work on that next.
Now some people will say but this is not a Dynamic named range and that is true but it does the same thing.
I do not deal with so called dynamic named ranges like the one you showed with all those 99999

Now there are other ways to do this also and that would be to use a Excel Table
If your not sure about Excel Tables. A Table is a Dynamic Range on a worksheet

Excel Tables always have a Name like Table1 or Table2 or you can name them Alpha or Bravo
What ever you want.

So in column 1 of the Table not column 1 of the sheet you could enter your values and then we could tell a script to look in column 1 of a Table or column 2 of a Table for all our values.
That could be defined as a dynamic Range.

But for now test my suggestion showed in previous post and see what happesn

And we can write code so users can load data from the userform to the sheet or from the sheet to the userform.

Our previous post shows how to load data from the sheet to the userform.
Well hope this helps you.

 

[Norie]

My Answer This

I think the OP's combobox is on a worksheet not a userform.

 

[My Aswer Is This]

My Answer This

I think the OP's combobox is on a worksheet not a userform.

Well he said this:

So over time, I created a dedicated worksheet (LookupLists) where all of the dynamic named ranges are and where all the comboboxes on my userforms are directed to. ​

 

[Norie]

From the very first post.

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