using rowsource to fill a combobox

[Excel-Programming]

I changed this procedure to remove the "for each" to fill the combo
box and tried using the row source as a value. I changed the column
count to 2 and the bound column to 2. The reason is I need the A and
B columns in the range not just the A column.

The change seems to work but on a sub-procedure it doesn't fill the
variable with the selected value so possibly the row source isn't
working? THANKS!
----
Sub UserForm_Initialize()

Dim lngLastRow As Long
Dim ws As Worksheet
Dim c As Range
Dim rng As Range

Set ws = ThisWorkbook.Worksheets("patients")
lngLastRow = Cells.Find(What:="*", After:=ws.Range("A1"),
SearchDirection:=xlPrevious).Row
Set rng = ws.Range("A1:B" & lngLastRow)

Me.ComboBox2.Clear
ComboBox2.ColumnCount = 2 ******
ComboBox2.RowSource = rng.Address ******
ComboBox2.BoundColumn = 2 *****
' For Each c In rng.Cells
' Me.ComboBox2.AddItem c.Value
' Next c

Me.ComboBox2.AddItem "All"
Me.ComboBox2.AddItem "Exit"

End Sub

 

[FSt1]

hi
you are setting the combo box rowsource correctly. i think its the last row
that is giving you problems. replace the find with this...
lngLastRow= Cells(Rows.Count, "b").End(xlUp).Row
and you could say...
ComboBox2.RowSource = ws.Range("A1:B" & lngLastRow)
thus eliminating the need for rng.....less typing....easier to read and
understand later.

regards
FSt1

 

[JLGWhiz]

It is more likely the way you are assigning the value to the variable than
the way you fill the list box. If you are using multiselect you cannot use
ListBox.Value to assign the value to a variable. You have to use ListIndex
or Selected properties.

 

[Janis R]

Hi
I tried the following and I am getting a type mismatch. thanks.
P.s. it stops on the line setting the rowsource . What is
mismatched?

Sub UserForm_Initialize()

Dim lngLastRow As Long
Dim ws As Worksheet
Dim c As Range
Dim rng As Range





Set ws = ThisWorkbook.Worksheets("patients")
lngLastRow = Cells(Rows.Count, "b").End(xlUp).Row

ComboBox2.ColumnCount = 2
ComboBox2.BoundColumn = 2

ComboBox2.RowSource = ws.Range("A1:B" & lngLastRow)


Me.ComboBox2.AddItem "All"
Me.ComboBox2.AddItem "Exit"

End Sub

hi
you are setting the combo box rowsource correctly. i think its the last row
that is giving you problems. replace the find with this...
lngLastRow= Cells(Rows.Count, "b").End(xlUp).Row
and you could say...
ComboBox2.RowSource = ws.Range("A1:B" & lngLastRow)
thus eliminating the need for rng.....less typing....easier to read and
understand later.

regards
FSt1

many thnks,

 

[Janis R]

I don't really want a multi-select. I want all the names stuffed
into one variable for printing a long list or only 1 name in the
variable either that or it exits so not a multi-select. It did work
when I filled the variable with the for each statement but I can't get
it to work with the rowsource. I don't even need two columns I could
have only one. I tried it with only one and it still stops on the
rowsource line with a type mistmatch. If the choice is all I guess I
would like the whole range in the variable and then I guess I will
have to parse each line for printing a record. Maybe that is not the
easiest way to do it?

thanks,

 

[Janis R]

It is possibly a problem with filling the variable however how come I
cannot get the listbox to at least show all the values?

 

[Janis R]

Maybe the row source doesn't work because I add two values to the row
source at the end and it doesn't like that in any case I will just go
back to using the for each loop.
I see your point it does the same thing. Sorry.

It is more likely the way you are assigning the value to the variable than
the way you fill the list box. If you are using multiselect you cannot use
ListBox.Value to assign the value to a variable. You have to use ListIndex
or Selected properties.

Wha