Populate ComboBox

[jeansb]

I have the code in my userform like this:

Private Sub UserForm_Initialize()
With Worksheets("sheet8")
Me.ComboBox1.List = .Range("b11:b500")
End With
End Sub

and the code in sheet1 like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c11:c500")) Is Nothing Then
If Target.Value = "" Then
UserForm1.Show
Else
Exit Sub
End If
End If
End Sub

I got the error said : "Runtime error '9' : Subscript out of range
Can someone point out for me what's the errors are ? how can I fix this ?
Thanks

 

[mikerickson]

If the error is in this line, check the sheet name and try this modification.

With ThisWorkbook.Worksheets("sheet8")

(Selection Change is pretty volatile, have you considered the Double Click event?)

 

[joefrench]

try something like this in your userform:

Private Sub UserForm_Initialize()
With Me.ComboBox1
     For Each c In Sheet1.Range("B11:B500")
            .AddItem c
     Next
End With
End Sub

 

[Datsmart]

This will also work:

Private Sub UserForm_Initialize()
    ComboBox1.List = Sheets("Sheet8").Range("b11:b500").Value
End Sub

 

[jeansb]

Thanks for your help but none of the above solve the problem.
when error pop up they mark the code "userform1.show" in yellow color !
Can you point me out what 's wrong ?

 

[joefrench]

Did you try my code? It worked fine for me in both Excel 2003 & 2007.

Sheet Module (as you had posted):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("c1:c500")) Is Nothing Then
If Target.Value = "" Then
UserForm1.Show
Else
Exit Sub
End If
End If
End Sub

UserForm (as I had posted):

Private Sub UserForm_Initialize()
With Me.ComboBox1
     For Each c In Sheet8.Range("B11:B500")
            .AddItem c
     Next
End With
End Sub

Note: My initial post had Sheet8.Range("B11:B500") written as Sheet1.Range("B11:B500") as I was testing on my system. Maybe why you didn't like it?

 

[Datsmart]

I would start by "Commenting out" the lines of your Initialization code and see if your userform will open. If it does, try just commenting a few lines to troubleshoot what line of code is causing your userform to error.
Post what you find if you still have questions.

 

[joefrench]

I agree with John. Many times I have run into the same problem that you have. Debugger makes it look like the code which calls your userform has the error but it's actually the UserForm_Initialize code that has the problem.

After just now re-looking at your "Initialize" code, it appears that you are trying to populate your combobox with the range rather than the range values.

 

[jeansb]

Thank you for your solution JoeFriend. I tried your code as you posted but it did not work either!
I tried to modified it as:

Dim c As Range
With Me.ComboBox1
For Each c In Sheets("Sheet8").Range("B11:B500").Value
.AddItem c
Next
End With

it doesn't work either !
I am using office 2000 Pro. I am pulling my hair out trying to figure what is going on but no help available !

 

[joefrench]

I do not have access to Excel 2000 and have never really done any macro/VBA in 2000 but maybe try changing to this.

Dim c As Range
With Me.ComboBox1
For Each c In Sheets("Sheet8").Range("B11:B500") 'removed .value from here
.AddItem c.Value 'and placed it here
Next
End With

i.e.
For each c (cell) in range
.AddItem c.Value (add the value of the cell to Me.ComboBox1).
Do this for the next cell.