Using vba to populate ComboBox list using named ranges

[terriertrip]

...
I've got some code that works when I explicitly define the range. I'm wondering why it won't using named ranges. It specifically throws "Could not List Property" error on "Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)" The form activation seems to accept using named ranges for ComboBox1. Is there a way to do this using named ranges?

Please Login or Register  to view this content.

[AlphaFrog]

The array variable is populated from one contiguous range. loop through each named range something like this...

Please Login or Register  to view this content.

[terriertrip]

Thanks, AlphaFrog. I'm still getting list property error which is due to the way I load the ComboBox1 in the userform. ComboBox1 data shows but as soon as a selection is made, the error is thrown. How would I load a combobox using named ranges? It's what I read off the MSDN, but to their credit, there was nothing related to using named ranges (EDIT: using named ranges in userform controls).

Please Login or Register  to view this content.

This is what I currently use to initialize Comboboxes, but it is somewhat nightmarish since it references cells instead of names.

Please Login or Register  to view this content.

[AlphaFrog]

I don't follow.

Did my previous code work?
Is this a different problem?
It seems like its a different combobox (combobox3) but with the same problem as before. If yes, then the previous code construct should work for this combobox.

[terriertrip]

No. The code did not work. Produces same error on the same line.
I thought it may be due to the way ComboBox1 was initially populated.

Please disregard the combobox3 statement; it was simply a sidenote of another example of populating a combobox I'm using in another routine.

[AlphaFrog]

No. The code did not work. Produces same error on the same line.

The line of code that errored in the original code no longer exists in the new code. Does the new code error on this line?
v = ws.Range(nm).Value

Are all the named ranges on this sheet?
ThisWorkbook.Sheets(msSHEET_LST)

Double check that all these named ranges actually exist and the Names are exact.
"Name1", "Name2", "Name3", "Name4"

[terriertrip]

The error (v(i, 2) =subscript out of range) is on the IfThen line
If v(i, 2) = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1) Then

Yes all named ranges exist on this sheet (AY:BD)

All Named ranges exist and are exact.

[AlphaFrog]

The error (v(i, 2) =subscript out of range) is on the IfThen line
If v(i, 2) = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1) Then

Do the named ranges have four columns each?

[terriertrip]

Yes.
Untitled.png

[AlphaFrog]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[terriertrip]

...
How can I use strictly named ranges and get the same result?

[AlphaFrog]

Try this. It assumes each named range is based on the OD letter using this naming convention:
OD_A
OD_B
OD_C
...etc

It gets the OD letter of the selected item from the second column of cboOD

Please Login or Register  to view this content.

[terriertrip]

I admire your late night fortitude but afraid that didn't work. I copied directly into the sample wb and it throws an error (v=Nothing). The variant range needs to be A:D since that is what will be the list within cboWt (combobox2).

[AlphaFrog]

I admire your late night fortitude but afraid that didn't work. I copied directly into the sample wb and it throws an error (v=Nothing). The variant range needs to be A:D since that is what will be the list within cboWt (combobox2).

Did you create named ranges using the naming convention i discussed?

each named range is based on the OD letter using this naming convention:
OD_A
OD_B
OD_C
...etc

[terriertrip]

Oh yes. I forgot about doing that. After renaming the ranges, the subscript error occurs on
.AddItem v(i, 2)

[AlphaFrog]

It worked for me.

[terriertrip]

Ok. I see what you were talking about, but that's only creating more issues.

This range of data is part of a large lookuplist with over a hundred columns. The lookuplist imports a master lookuplist database, so every time a user opens a new workbook, a master lookuplist is transferred to the workbook and ranges are named - everything done by vba.

I would then need additional code to select those ranges you are suggesting, and name them strictly using vba. So I'm not sure if this is the best direction.