Load Values from a Range into a List Box
 

I have a workheet that contains a list on several companies in Col. A. When
my Userform is initialized it finds the lastrow in Col. A and then sets the
CompanyData Range. I am getting an error indicated below. I know I am doing
it wrong, but I don't know the best way to add the range to the list.

Private Sub UserForm_Initialize()

Dim CompanyData As Range
Dim LastRow As Long

'finds last row in company Column
LastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

'company list range
Set CompanyData = Sheets("Sheet2").Range("A1:A" & LastRow)

'loads company data range into Company ComboBox
ERROR = Me.cboCompany.ControlSource = "CompanyData"

End Sub

--
Cheers,
Ryan

Load Values from a Range into a List Box
 

I'd use:

Me.cboCompany.list = CompanyData.value
or
Me.cboCompany.Rowsource = CompanyData.address(external:=true)

The .controlsource is like the linkedcell--not where the list should be picked
up from.


RyanH wrote:

I have a workheet that contains a list on several companies in Col. A. When
my Userform is initialized it finds the lastrow in Col. A and then sets the
CompanyData Range. I am getting an error indicated below. I know I am doing
it wrong, but I don't know the best way to add the range to the list.

Private Sub UserForm_Initialize()

Dim CompanyData As Range
Dim LastRow As Long

'finds last row in company Column
LastRow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

'company list range
Set CompanyData = Sheets("Sheet2").Range("A1:A" & LastRow)

'loads company data range into Company ComboBox
ERROR = Me.cboCompany.ControlSource = "CompanyData"

End Sub

--
Cheers,
Ryan

--

Dave Peterson