Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using rowsource to fill a combobox
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 --------- here is a line from the sub-procedure where it doesn't compile: thanks again, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using rowsource to fill a combobox
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 "Excel-Programming" wrote: 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 --------- here is a line from the sub-procedure where it doesn't compile: thanks again, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
using rowsource to fill a combobox
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. "Excel-Programming" wrote: 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 --------- here is a line from the sub-procedure where it doesn't compile: thanks again, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
using rowsource to fill a combobox
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 On Jan 20, 4:39 pm, FSt1 wrote: 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 "Excel-Programming" wrote: 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 --------- here is a line from the sub-procedure where it doesn't compile: thanks again, many thnks, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
using rowsource to fill a combobox
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, On Jan 20, 5:27 pm, JLGWhiz wrote: 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. "Excel-Programming" wrote: 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 --------- here is a line from the sub-procedure where it doesn't compile: thanks again, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
using rowsource to fill a combobox
It is possibly a problem with filling the variable however how come I
cannot get the listbox to at least show all the values? On Jan 20, 5:27 pm, JLGWhiz wrote: 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. "Excel-Programming" wrote: 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 --------- here is a line from the sub-procedure where it doesn't compile: thanks again, |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
using rowsource to fill a combobox
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. On Jan 20, 5:27 pm, JLGWhiz wrote: 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. "Excel-Programming" wrote: 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 --------- here is a line from the sub-procedure where it doesn't compile: thanks again, Wha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ComboBox.RowSource? | Excel Programming | |||
Combobox Rowsource | Excel Programming | |||
Combobox rowsource | Excel Programming | |||
How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming | |||
combobox rowsource | Excel Programming |