Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Combobox
I am trying to fill a combobox3 with values that are in another sheet.
The combobox is on a userform. The condition is that i have a combobox2 above this one with 2 columns in it. 1 Column has a Name, the other has a Numerical value, and BOTH are displayed in the Combobox2 like [name 123] I am assuming Column 1 is the name, and column2 is the number ? In the Combobox3 DropButton_Click event i want to have something logical like: ' Code dim rngfound as cell dim i as long For i = 3 - 20 with sheet4.Row2 ..select Find Combobox2.Column2.Value if rngfound ="" then msgbox "No values exist" else Combobox3.additem rngfound.Offset( i, 0) ' So i want the values that are from 1 - 20 rows DOWN from the matching value in row 2 to the Combobox2.Column2.value Does this make sense?? How can i succesully code the above to work? Corey.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Combobox
I didn't find your request real clear, but believe you are looking for
something like this. Private Sub ComboBox3_DropButtonClick() Dim str1 As String Dim str2 As String Dim str3 As String Dim iPos As Integer Dim rng As Range str1 = ComboBox2.RowSource iPos = InStr(str1, "!") str2 = Left(str1, iPos - 1) str3 = Right(str1, Len(str1) - iPos) Set rng = Sheets(str2).Range(str3) Set rng = rng.Offset(ComboBox2.ListIndex + 1, 0) Set rng = rng.Resize(20, 2) ComboBox3.RowSource = str2 & "!" & rng.Address End Sub Hth, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Combobox
This is better.
Private Sub ComboBox3_DropButtonClick() Dim rng As Range Set rng = Range(ComboBox2.RowSource) Set rng = rng.Offset(ComboBox2.ListIndex + 1, 0) Set rng = rng.Resize(20, 2) ComboBox3.RowSource = rng.Address End Sub Hth, Merjet |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Combobox
Merjet,
thank you for the reply. I placed your code in and got an erro at this line: str2 = Left(str1, iPos - 1) I do not understand it and cannot solve this ? Corey.... "merjet" wrote in message oups.com... I didn't find your request real clear, but believe you are looking for something like this. Private Sub ComboBox3_DropButtonClick() Dim str1 As String Dim str2 As String Dim str3 As String Dim iPos As Integer Dim rng As Range str1 = ComboBox2.RowSource iPos = InStr(str1, "!") str2 = Left(str1, iPos - 1) str3 = Right(str1, Len(str1) - iPos) Set rng = Sheets(str2).Range(str3) Set rng = rng.Offset(ComboBox2.ListIndex + 1, 0) Set rng = rng.Resize(20, 2) ComboBox3.RowSource = str2 & "!" & rng.Address End Sub Hth, Merjet |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Combobox
Private Sub ComboBox3_DropButtonClick()
Dim rng As Range Set rng = Range(ComboBox2.RowSource) '; <== Error here Set rng = rng.Offset(ComboBox2.ListIndex + 1, 0) Set rng = rng.Resize(20, 2) ComboBox3.RowSource = rng.Address End Sub For the Numerical value in the Combobox2 (Which is on the RIGHT sode of the 2 columns), should the BOUND Column be 1 or 2 ? The error (mouse over) says Combobox2.RowSource = "" Corey.... "merjet" wrote in message oups.com... This is better. Private Sub ComboBox3_DropButtonClick() Dim rng As Range Set rng = Range(ComboBox2.RowSource) Set rng = rng.Offset(ComboBox2.ListIndex + 1, 0) Set rng = rng.Resize(20, 2) ComboBox3.RowSource = rng.Address End Sub Hth, Merjet |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling Combobox
BoundColumn is only used to determine ComboBox.Value. Since the latter
is not used in the code I wrote, it is not an issue for said code. The line of code where you the got error works fine for me. Maybe you got the error because you have not set the Combobox2.RowSource property (you are populating Combobox2 in a different way). Try one of these just before that line: Debug.Print Combobox2.RowSource MsgBox Combobox2.RowSource I get Sheet1!A1:B25. Hth, Merjet |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filling Textboxes from Combobox selection | Excel Programming | |||
combobox filling problem | Excel Programming | |||
filling a simple combobox | Excel Programming | |||
searching for a combobox.value and filling in textboxes from results | Excel Programming | |||
Filling a ComboBox? | Excel Programming |