Filling Combobox

[Corey]

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

 

[merjet]

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

 

[merjet]

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

 

[Corey]

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

 

[Corey]

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


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

 

[merjet]

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