Populating ComboBox Methods

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

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





.
Click to expand...
 
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
 
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
 
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
 
Back
Top