error message on populating combobox

[Valeria]

Dear experts,
I need to populate a combobox in VBA with the values coming from a database,
chosen according to the input the user makes on a previous combobox. (I was
using the indirect funtion outside VBA).
However, I get an error from the code.... here it is:

Private Sub Userform_Initialize()
Dim rng As Range
Dim End_Row As Integer
Dim Begin_Row As Integer
Dim i As Integer

i = 0

Do
i = i + 1
Loop Until Worksheets("Data").Cells(i, 1) = Worksheets("Choix").Cells(4, 1)
Begin_Row = i

Do
i = i + 1
Loop Until Worksheets("Data").Cells(i, 1) <> Worksheets("Choix").Cells(4, 1)
End_Row = i - 1

Set rng = Range((Worksheets("Data").Range("B", Begin_Row)),
Worksheets("Data").Range("B", End_Row))

Me.ComboBox1.List = rng.Value

End Sub

I am using Excel 2003.
Could you please help?
Many thanks!
Kind regards,

 

[Mike H]

I gave you the answer to this 3 days ago, did you try it

Private Sub Userform_Initialize()
Dim End_Row As Long
Dim Begin_Row As Long
Dim i As Long
i = 0
Do
i = i + 1
Loop Until Worksheets("Data").Cells(i, 1) _
= Worksheets("Choix").Cells(4, 1)
Begin_Row = i
Do
i = i + 1
Loop Until Worksheets("Data").Cells(i, 1) _
<> Worksheets("Choix").Cells(4, 1)
End_Row = i - 1
Me.ComboBox1.List = Worksheets("Data").Range( _
"B" & Begin_Row & ":B" & End_Row).Value
End Sub


Mike

 

[Per Jessen]

Hi

What is the error message when you click Debug?
Which line is highlighted?

Regards,
Per

 

[Mike H]

or to use your method with the syntax corrected

Set rng = Range(Worksheets("Data").Range("B" & Begin_Row), _
Worksheets("Data").Range("B" & End_Row))
Me.ComboBox1.List = rng.Value

Mike

 

[Valeria]

Hi Mike
thanks, for some reasons I cannot find my previous post any longer - I
thought it had not gone through!
I have tried your code, it gives me the error "Could not set the List
Property. Invalid property array index"...
Thanks,
Kind regards

 

[Valeria]

Correcting what I said: sometimes I have just 1 entry in the list, and that's
when it gives the error; otherwise it works beautifully. Is there a way to
manage the 1 entry lists?
Thanks!

 

[Mike H]

Hi,

The problem is with your data and not with the code.

Cell A4 on worksheet Choix contains a value.

the code loops down column A of worksheet Data until it finds that value and
sets the variable Begin_row.

the loop then continues until it founds a value different to A4 and sets the
variable End_Row which is 1 row back.

the combobox is then populated with the data between Begin_Row and End_Row-1
from column B

What is happening is that the code isn't finding either end_row or both and
my guess is that end_row is the problem

Manually check your data. find the cell you 'think' is begin row in the data
sheet (say A7) and enter this formula

=A7=Choix!A4

it should return true

Repeat this for other values in column A and if any you think should return
true
return false then that's the issue. The most common causes are

1. Numbers as text
2. rogue spaces at the beginnin or end of the string

If spaces are the issue then modify your code like this

Private Sub Userform_Initialize()
Dim End_Row As Long
Dim Begin_Row As Long
Dim i As Long
i = 0
Do
i = i + 1
Loop Until Trim(Worksheets("Data").Cells(i, 1)) _
= Trim(Worksheets("Choix").Cells(4, 1))
Begin_Row = i
Do
i = i + 1
Loop Until Trim(Worksheets("Data").Cells(i, 1)) _
<> Trim(Worksheets("Choix").Cells(4, 1))
End_Row = i - 1
Me.ComboBox1.List = Worksheets("Data").Range( _
"B" & Begin_Row & ":B" & End_Row).Value
End Sub



Mike

 

[Mike H]

Hi,

With 1 entry in the list end_row ends up less then begin_row so modify your
code like this

Private Sub Userform_Initialize()
Dim End_Row As Long
Dim Begin_Row As Long
Dim i As Long
i = 0
Do
i = i + 1
Loop Until Worksheets("Data").Cells(i, 1) _
= Worksheets("Choix").Cells(4, 1)
Begin_Row = i
Do
i = i + 1
Loop Until Worksheets("Data").Cells(i, 1) _
<> Worksheets("Choix").Cells(4, 1)
End_Row = i - 1
If End_Row > Begin_Row Then
Me.ComboBox1.List = Worksheets("Data").Range( _
"B" & Begin_Row & ":B" & End_Row).Value
Else
Me.ComboBox1.AddItem Worksheets("Data").Range("B" & Begin_Row)
End If
End Sub

Mike

 

[Valeria]

Hi Mike,
I have checked the data as you explained and everything looks OK; the Begin
row is correct and corresponding to the entry in the "choix" worksheet.
The Last Row is also correct, when there is a single line entry then it is
the same as Begin Row, and this is when the code is giving problems.
e.g.
apples rome
apples venice
pears paris
banana new york

The "pears" and "banana" rows will give a problem because the colums B list
is just 1 cell...

I have also checked spaces however I haven't found any; to make extra sure I
have also tried your code below but it still does not work...

Thanks!

 

[Valeria]

Hi Mike,
this did the trick - works beautifully, thank you!!!!
Have a very nice we

 

[Mike H]

Glad i could help and thanks for the feedback

Hi Mike,
this did the trick - works beautifully, thank you!!!!
Have a very nice we