Why when I use the below code... I get the error Could
not set the rowsource property. Invalid property use.



Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub


-----Original Message-----
forgot to take out the loop in the second one:

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub

or to use Worksheets(1)

Private Sub UserForm_Initialize()
sNm = Worksheets(1).name
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET('" & sNm & "'!A1,,,COUNTA('" & sNm & "'!A:A))"
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
End Sub

--
Regards,
Tom Ogilvy


Tom Ogilvy wrote in message
...
Does excel automatically know what you want to populate
the combobox with
similar to knowing how many sheets in a workbook? No.
If you want to
create a defined name that is defined with a dynamic
range, then I suppose
you could view it as having a similar perspective.

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.AddItem cell.Value
Next

End Sub

or

Private Sub UserForm_Initialize()
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))"
For Each cell In Range("List1")
ComboBox1.RowSource = ThisWorkbook.Name & "!List1"
Next

End Sub




--
Regards,
Tom Ogilvy


Todd Huttenstine
wrote in message
...
No, its a different one without having to dimension
anything.


-----Original Message-----
The below method adds sheet names to the specified
combobox. Is it possible that I can use the same or
similar method to add values in a range to a
specified
combobox? The below method is very to the point and
simple.

For i = 5 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next
.





.