Combobox list source

[Chris Thompson]

Does anybody know if it is possible to achieve this?

I want the list source to a combobox to be two text strings (Solo &
Main) followed by the text entries in a column of cells (A2 downwards)
so that I end up with a combobox list that looks like this

Solo
Main
A1023452
A1238748
B126534
B128765
etc

where the A1023452 values are the values from cells A2 downwards.

Chris Thompson.

 

[Dave Peterson]

Something like this:

Option Explicit
Private Sub UserForm_Initialize()

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ComboBox1
.AddItem "Solo"
.AddItem "Main"
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
End With

End Sub

 

[Chris Thompson]

Dave

Thanks for your help.
Where in the code view do I insert this portion of code as I have
tried it but it doesn't seem to be working (my mistake probably).

Chris.

 

[Dave Peterson]

I added it to the initialization of the form. But you'll want to add to the
portion of your code that adds stuff to the listbox. (if you assign manually,
get rid of that and try the UserForm_Initialize procedure (code is behind the
userform.)

In fact, you could have the code just make sure you don't do it manually:
Option Explicit
Private Sub UserForm_Initialize()

Dim myCell As Range
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

With Me.ComboBox1
.RowSource = ""
.AddItem "Solo"
.AddItem "Main"
For Each myCell In myRng.Cells
.AddItem myCell.Value
Next myCell
End With

End Sub

(I added one line (.rowsource = "") to the existing suggestion.)