Adding Items To a combo Box

[LoveCandle]

Hi everybody,

I have a form in VBA containing a ComboBox >> what I want is adding items to that ComboBox .. The Items is a list availble in range (A1:A50).

Can you please help me in that case.

Thank you soooo much,

[Bob Phillips]

Set the RowSource property to A1:A50

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LoveCandle" <[email protected]> wrote
in message news:[email protected]...
>
> Hi everybody,
>
> I have a form in VBA containing a ComboBox >> what I want is adding
> items to that ComboBox .. The Items is a list availble in range
> (A1:A50).
>
> Can you please help me in that case.
>
> Thank you soooo much,
>
>
> --
> LoveCandle
> ------------------------------------------------------------------------
> LoveCandle's Profile:
http://www.excelforum.com/member.php...o&userid=28612
> View this thread: http://www.excelforum.com/showthread...hreadid=530403
>

[LoveCandle]

Thank you som much Mr. Bob,,

But at the end of that range there are some blank cells which are waiting to be filled in the future .. and I don't want blank cells to appear in the ComboBox >> I want only filled cells to appear..

Is there any way for that???

[Bob Phillips]

You will have to either

- create a list in the worksheet with no blanks using formulae

- go through the list and only AddItem the non-blank entries.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"LoveCandle" <[email protected]> wrote
in message news:[email protected]...
>
> Thank you som much Mr. Bob,,
>
> But at the end of that range there are some blank cells which are
> waiting to be filled in the future .. and I don't want blank cells to
> appear in the ComboBox >> I want only filled cells to appear..
>
> Is there any way for that???
>
>
> --
> LoveCandle
> ------------------------------------------------------------------------
> LoveCandle's Profile:
http://www.excelforum.com/member.php...o&userid=28612
> View this thread: http://www.excelforum.com/showthread...hreadid=530403
>

[LoveCandle]

Hi Mr. Bob

After posting my question about the way to get the cells that have data only in the ComboBox >> I remembered one way I have used before with the List feature in Validation property >> I tried the same way with ComboBox and it worked perfectly.

My way is using the following formula in the SourceRow instead of the normal source (A1:A50), and it will give us the cells that have data only.

A1:INDIRECT(ADDRESS(COUNTIF(A1:A50,"<>0"),1))

* The second part of the previous formula give us the address of the last cell that is not blank.

Remarks: if the list is not in the first column and not starting from the first row, we can adjust it to be like the following one:

P9:INDIRECT(ADDRESS(COUNTIF(P9:P108,"<>0")+8,16))

I hope that everybody benefits from this,

bye,