I am trying to populate a ComboBox RowSource in a form with a named range
from another workbook. I'm a little stumped. Any suggestions?

Here is one that I use in a procedure. It uses a variable length row source.

Private Sub UserForm_Initialize()
With Worksheets(3)
maxRw = .Cells(Rows.Count, 1).End(xlUp).Row
x = .Cells(maxRw, 1).Address
UserForm3.Caption = "SELECT STREET NAME"
With UserForm3.ListBox1
.RowSource = "Sheet3!$A$2:" & x 'This refers to other sheet range
.BoundColumn = 1
.ColumnCount = 1
.ListStyle = fmListStyleOption
End With
End With
End Sub


"Caleb Runnels" wrote:

> I am trying to populate a ComboBox RowSource in a form with a named range
> from another workbook. I'm a little stumped. Any suggestions?
>
>
>

It's not quite exactly what I'm trying to do. That example would work fine
if I needed to use a different sheet in the same workbook, but I need to use
a complete different workbook...aka...different Excel file.


"JLGWhiz" <(E-Mail Removed)> wrote in message
news:F5D06741-FDCF-4971-A330-(E-Mail Removed)...
> Here is one that I use in a procedure. It uses a variable length row
> source.
>
> Private Sub UserForm_Initialize()
> With Worksheets(3)
> maxRw = .Cells(Rows.Count, 1).End(xlUp).Row
> x = .Cells(maxRw, 1).Address
> UserForm3.Caption = "SELECT STREET NAME"
> With UserForm3.ListBox1
> .RowSource = "Sheet3!$A$2:" & x 'This refers to other sheet range
> .BoundColumn = 1
> .ColumnCount = 1
> .ListStyle = fmListStyleOption
> End With
> End With
> End Sub
>
>
> "Caleb Runnels" wrote:
>
>> I am trying to populate a ComboBox RowSource in a form with a named range
>> from another workbook. I'm a little stumped. Any suggestions?
>>
>>
>>

Assuming the other workbook is open then:

..RowSource =
workbooks("Otherbook.xls").Names("MyName").ReferstoRange(0,0,xlA1,True)

--
Regards,
Tom Ogilvy


"Caleb Runnels" wrote:

> It's not quite exactly what I'm trying to do. That example would work fine
> if I needed to use a different sheet in the same workbook, but I need to use
> a complete different workbook...aka...different Excel file.
>
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news:F5D06741-FDCF-4971-A330-(E-Mail Removed)...
> > Here is one that I use in a procedure. It uses a variable length row
> > source.
> >
> > Private Sub UserForm_Initialize()
> > With Worksheets(3)
> > maxRw = .Cells(Rows.Count, 1).End(xlUp).Row
> > x = .Cells(maxRw, 1).Address
> > UserForm3.Caption = "SELECT STREET NAME"
> > With UserForm3.ListBox1
> > .RowSource = "Sheet3!$A$2:" & x 'This refers to other sheet range
> > .BoundColumn = 1
> > .ColumnCount = 1
> > .ListStyle = fmListStyleOption
> > End With
> > End With
> > End Sub
> >
> >
> > "Caleb Runnels" wrote:
> >
> >> I am trying to populate a ComboBox RowSource in a form with a named range
> >> from another workbook. I'm a little stumped. Any suggestions?
> >>
> >>
> >>
>
>
>

Just a typo alert:

..RowSource = workbooks("Otherbook.xls").Names("MyName") _
.ReferstoRange.address(0,0,xlA1,True)

(added .address)

Tom Ogilvy wrote:
>
> Assuming the other workbook is open then:
>
> .RowSource =
> workbooks("Otherbook.xls").Names("MyName").ReferstoRange(0,0,xlA1,True)
>
> --
> Regards,
> Tom Ogilvy
>
> "Caleb Runnels" wrote:
>
> > It's not quite exactly what I'm trying to do. That example would work fine
> > if I needed to use a different sheet in the same workbook, but I need to use
> > a complete different workbook...aka...different Excel file.
> >
> >
> > "JLGWhiz" <(E-Mail Removed)> wrote in message
> > news:F5D06741-FDCF-4971-A330-(E-Mail Removed)...
> > > Here is one that I use in a procedure. It uses a variable length row
> > > source.
> > >
> > > Private Sub UserForm_Initialize()
> > > With Worksheets(3)
> > > maxRw = .Cells(Rows.Count, 1).End(xlUp).Row
> > > x = .Cells(maxRw, 1).Address
> > > UserForm3.Caption = "SELECT STREET NAME"
> > > With UserForm3.ListBox1
> > > .RowSource = "Sheet3!$A$2:" & x 'This refers to other sheet range
> > > .BoundColumn = 1
> > > .ColumnCount = 1
> > > .ListStyle = fmListStyleOption
> > > End With
> > > End With
> > > End Sub
> > >
> > >
> > > "Caleb Runnels" wrote:
> > >
> > >> I am trying to populate a ComboBox RowSource in a form with a named range
> > >> from another workbook. I'm a little stumped. Any suggestions?
> > >>
> > >>
> > >>
> >
> >
> >

--

Dave Peterson