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

 

[jeansb]

If I try to take out the UserForm_Initialize() code the Worksheet_SelectionChange working fine so it is the initialize code cause the problem as Joefrench said .
now i am trying to figure out which line is causing the problem ! I'll post back and let you know. thank you all for your helps !

 

[jeansb]

this is my userform code:

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

and it doesn't work either ! I tried to comment out each line of the code and see which one cause the problem.. i'll post back .

 

[Datsmart]

Your code works in my Test page.
What do you have in Sheet8, range B11:B500?
Try shortening the range temporarily and see if it makes a difference.

 

[joefrench]

What version are you running John?

 

[Datsmart]

I am running Excel 2000 Home Edition. (9.0)

 

[joefrench]

Interesting. Is it possible that the OP has a VBA reference missing (not selected)?

 

[jeansb]

I tried to comment each line in UserForm_Initialize() but it is still have the same problem, and I still have no idea what cause the code in UserForm_Initialize() NOT populate the userform. Since Joefrench mention something about VBA Reference, What are they ? how do I check them ? or something else ?
Thank you all for your respond !

 

[jeansb]

P.S
the Sheet8, column b11:b500 contains 500 data of book- titles in my store.

 

[joefrench]

In your VB Editor, go to Tools>References. Looks like the image below in 2003:


Post what you have checked off. Also, just curious, is the name of the sheet of the range that you're trying to populate your combobox with actually named Sheet8?

Give this one a quick try [changed Sheets("Sheet8") to Sheet8]:

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

 

[jeansb]

Thank you so much for your info on setting the VBA reference joefrench ! It works like a charm now ! what my VBA reference missing is a check off on Microsoft Excel Object Library!
Thanks all for your support ....