ComboBox - named dynamic range as rowsource -- How do I make the items all caps?

Question

I have a ComboBox in one Userform, which uses a named dynamic range in a worksheet as rowsource.

This list is simply an all-in-upper-case version of another named dynamic range and placed as another column side-by-side to the proper-case version of the same list.

To make things minimalistic and simple, I would like to eliminate this column (named range) from the worksheet and use the proper-case dynamic range to be used in this ComboBox's rowsource but somehow convert the items into all upper case.

How can I do this?

Maki

Answer 1

First of all, you should convert your range to a table and convert the dynamic range name to a normal range name (the table takes care of the dynamics).

You could read the range into an array first and convert it in VBA:

Dim vArr As Variant

Dim lRow As Long

vArr = Worksheets("TheSheet").Range("TheDynamicRangeName").Value

For lRow = LBound(vArr,1) To UBound(vArr, 1)

vArr(lRow,1) = UCase(vArr(lRow,1))

Next

ComboBox1.List = vArr

Answer 2

When a range is exactly one cell, reading that range's value into a variant does NOT change the variant into an array of values, but rather the value itself is dumped into the variant. You can use the VBA IsArray function to catch that situation.

Answer 3

Hi again, Jan.

The previous problem with Error 13 (Type mismatch) disappeared once I added more dummy records.

It could have been an issue with having only one record, thus LBound(vArr, 1) and UBound(vArr, 1) being the same.  :P

I see the stupidity of trying to use array when you only have one record but is there a way to overcome this issue?  Something that can handle array with item numbers from 1 to anything above?

Thanks for your input.

Maki

Answer 4

Hi Jan and thank you so much for taking time to answer.

I did pretty much the same as your suggestion, except for the Worksheet name and the Dynamic Range Name and placed under Userform_Initialize() subroutine.

Private Sub UserForm_Initialize()

Dim vAr As Variant

Dim lRow As Long

vArr = Worksheets("Comps").Range("CompName").Value

For lRow = LBound(vArr, 1) To UBound(vArr, 1)

vArr(lRow, 1) = UCase(vArr(lRow, 1))

Next

Me.ComboBox1.List = vArr

End Sub

With this, I get Error 13 (Type mismatch).  I fiddled around changing Variant to String (as the range to convert to all upper case is String) and Long to Integer but no luck.

What am I doing wrong?

Maki