Populating ComboBox Methods
 

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

Populating ComboBox Methods
 

Isn't that the method you presented in your original post?

for each cell in Worksheets(8).Range("A1:A100"). _
SpecialCells(xlConstants)
Combobox1.AddItem cell.Value
Next

--
Regards,
Tom Ogilvy


Todd Huttenstine wrote in 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

Populating ComboBox Methods
 

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
.

Populating ComboBox Methods
 

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
.

Populating ComboBox Methods
 

Thank you.

Works.


-----Original 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
.



.

Populating ComboBox Methods
 

Yes below is what I used, I just didnt want to have to
create a variable...

Dim rng As Range
With Worksheets(8)
Set rng = .Range("C1:C100")
End With
For Each Cell In rng
If Cell.Value < "" Then
ComboBox23.AddItem Cell.Value
End If
Next



-----Original Message-----
Isn't that the method you presented in your original post?

for each cell in Worksheets(8).Range("A1:A100"). _
SpecialCells(xlConstants)
Combobox1.AddItem cell.Value
Next

--
Regards,
Tom Ogilvy


Todd Huttenstine
wrote in 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


.

Populating ComboBox Methods
 

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
.

Populating ComboBox Methods
 

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
.





.

Populating ComboBox Methods
 

I can't say. It works fine for me.

--
Regards,
Tom Ogilvy

Todd Huttenstine wrote in message
...
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
.





.