End of Combo Box

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

I have written for help on this previously, although the subject was a
little different. I have gone back to the original "easier" to understand
(for me) code so that if you provide a solution, I might be able to
understand.

The Combo Box has a finite number of entries, but I do not want to see those
that are "blank".
"CombinedData" is a named field that has the following code:
=BankData($AR$2:$AR$999) ' This would show the blank lines in my UserForm
So I changed the named field to look like the following:
=OFFSET(BankData!$AR$2,0,0,COUNTA(BankData!$AR$2:$AR$999),1)
and changed Data Validation to include the List =CombinedData.
There was no change in the "look" of the Combo Box values

Is there a way for me to restrict the values in the Combo Box to "Non-Blank"
values?

Thanks,
Bernie

Option Explicit

Private Sub CombinedBankNames_Change()
boxvalue = CombinedBankNames.Value
Call MoveBankData
End Sub

Private Sub UserForm_Initialize()
Dim MyArray As Variant
MyArray = Range("CombinedData")
CombinedBankNames.List = MyArray
End Sub
 
Well, I have discovered what the problem is, but I still don't know how to
solve it. Those "Blank" cells have formulas in them, that evaluate to "".
So still, how do I make this work?

Bernie
 
Maybe you could just loop through the range and add it to the combobox if it's
not zero length.

dim myCell as range

for each mycell in worksheets("bankdata").range("ar2:ar999").cells
if mycell.value = "" then
'do nothing
else
me.CombinedBankNames.additem mycell.value
end if
next mycell

(still in the _initialize code)

(watch for typos. I composed in the email and didn't test.)
 
Thanks, Dave!

You sure make it look simple. This fix also works. My Combo box works
really well now.

Bernie
 
Back
Top