End of Combo Box

[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

[bw]

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


"bw" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Dave Peterson]

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.)

bw wrote:
>
> 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

--

Dave Peterson

[bw]

Thanks, Dave!

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

Bernie

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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.)
>
> bw wrote:
> >
> > 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
>
> --
>
> Dave Peterson