I need to create a dropdown combobox that has the names from Columb B in
Sheet "Names"

The combobox is in Form FrmMTCLog
the drop down is: combobox1
I need the combobox1 to have a list of all the names from Sheet"Names" Cell
b2 and down to the last filled in Column.

One way:

In the _initialize procedure:

with worksheets("Names")
me.combobox1.list = .range("B2",.cells(.rows.count,"B").end(xlup)).value
end with

(last filled in column B, right???)



Benjamin wrote:
>
> I need to create a dropdown combobox that has the names from Columb B in
> Sheet "Names"
>
> The combobox is in Form FrmMTCLog
> the drop down is: combobox1
> I need the combobox1 to have a list of all the names from Sheet"Names" Cell
> b2 and down to the last filled in Column.

--

Dave Peterson

Private Sub UserForm_Initialize()
rng = Sheets("Names").Cells(Rows.Count, 2).End(xlUp).Address
Me.FrmMTCLog.RowSource = "Names!$B$2:" & rng
End Sub




"Dave Peterson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> One way:
>
> In the _initialize procedure:
>
> with worksheets("Names")
> me.combobox1.list = .range("B2",.cells(.rows.count,"B").end(xlup)).value
> end with
>
> (last filled in column B, right???)
>
>
>
> Benjamin wrote:
>>
>> I need to create a dropdown combobox that has the names from Columb B in
>> Sheet "Names"
>>
>> The combobox is in Form FrmMTCLog
>> the drop down is: combobox1
>> I need the combobox1 to have a list of all the names from Sheet"Names"
>> Cell
>> b2 and down to the last filled in Column.
>
> --
>
> Dave Peterson

Easiest way is to do some work on the sheet first.

============
CREATE A DYNAMIC NAMED RANGE OF OPTIONS
1) Open the Named Range box (Insert > Name > Define)
2) In the "Names in Workbook" line, type in a name...for instance OPTIONS
3) At the bottom in the "Refers To" line, enter this dynamic formula:

=INDEX(Sheet2!$B:$B, 2):INDEX(Sheet2!$B:$B, COUNTIF(Sheet2!$B:$B,">"""))

4) Click ADD, then Close

You now have a named range called "Options" the keeps itself expanded to
include all the items in Sheet2, range B2>bottom of that data range. You
won't ever have to edit it.

============
CONNECT YOUR COMBOBOX TO THE NAMED RANGE
1) Right-Click on your combobox and select PROPERTIES
2) In the LISTFILLRANGE enter the value of OPTIONS


Your combobox is now linked permanently to the dynamic range "Options".

Does that help?

--
"Actually, I *am* a rocket scientist." -- JB
(www.MadRocketScientist.com)

Your feedback is appreciated, click YES if this post helped you.


"Benjamin" wrote:

> I need to create a dropdown combobox that has the names from Columb B in
> Sheet "Names"
>
> The combobox is in Form FrmMTCLog
> the drop down is: combobox1
> I need the combobox1 to have a list of all the names from Sheet"Names" Cell
> b2 and down to the last filled in Column.
>
>

Using the .rowsource property is another way.

But I would use something like:

Private Sub UserForm_Initialize()
Dim rng as Range

with worksheets("Names")
set rng = .range("B2",.Cells(.Rows.Count, "B").End(xlUp))
end with

'I think the form was named FrmMTCLog.
me.combobox1.RowSource = rng.address(external:=true)

End Sub

Then if the worksheet name changed, I'd only have one spot to fix (or use the
Codename for that sheet and not have to worry???).





JLGWhiz wrote:
>
> Private Sub UserForm_Initialize()
> rng = Sheets("Names").Cells(Rows.Count, 2).End(xlUp).Address
> Me.FrmMTCLog.RowSource = "Names!$B$2:" & rng
> End Sub
>
> "Dave Peterson" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > One way:
> >
> > In the _initialize procedure:
> >
> > with worksheets("Names")
> > me.combobox1.list = .range("B2",.cells(.rows.count,"B").end(xlup)).value
> > end with
> >
> > (last filled in column B, right???)
> >
> >
> >
> > Benjamin wrote:
> >>
> >> I need to create a dropdown combobox that has the names from Columb B in
> >> Sheet "Names"
> >>
> >> The combobox is in Form FrmMTCLog
> >> the drop down is: combobox1
> >> I need the combobox1 to have a list of all the names from Sheet"Names"
> >> Cell
> >> b2 and down to the last filled in Column.
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson