Use RowSource in ComboBox

Originally Posted by Len Silva

I see a lot of advice online not to use RowSource in a Userform ComboBox, but rather build the list in the code, but no explanation of why.

My list is 84 lines long and from a named range. It is the first column in my database and will never grow beyond 84 rows. Is that something I should be concerned about as I stumble along in this project?

Thanks

People overstate things sometimes. Either method is fine but there are differences in what you can and cannot do with each.

With the .RowSource method, you are limited in Adding\Removing\Editing the list. To edit the list, you would have to edit the values on the sheet. Then the list would automatically update with what is on the sheet. You couldn't add\remove\edit the list directly without changing the sheet. Also, the RowSource has to be one contiguous range.

The .List method allows for much more flexibility in directly manipulating the list and isn't directly tied to any one range on the sheet.

The RowSorce method is perfectly fine to use if you want to have a fix list directly linked to one contiguous range on the sheet. Which method to use just depends on your requirements.

I have personally never had a problem with using RowSource in a situation like yours.

However, instead of assuming that something will NEVER change, try to build flexibility into your overall approach, to allow things to change without needing a major re-write.

Dynamic named ranges:
So, for example, instead of having static named ranges, create dynamic ones. This way if 84 rows becomes 83 or 85, it will still work.

With header in cell A1, the data starting in A2 and there being no empty cells within column A until you get to the end of the data,
use this as your named range RefersTo formula:
=OFFSET(mySheetName!$A$1,1,0,COUNTA(mySheetName!$A:$A)-1,1)
(simply amend mySheetName to match your own)

In the attached workbook this formula (which counts the number of non-empty cells in the named range ) is in cell B1:
=COUNTA(myName)
Add a few values immediately below A84, and then check the value of B1

You are welcome, thank for the reps