Populate ComboBox

Status
Not open for further replies.
J

jeansb

New Member
Joined
Jul 20, 2003
Messages
19
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
If the error is in this line, check the sheet name and try this modification.
Code: 
With ThisWorkbook.Worksheets("sheet8")


(Selection Change is pretty volatile, have you considered the Double Click event?)
 
Upvote 0
try something like this in your userform:

Code: 
Private Sub UserForm_Initialize()
With Me.ComboBox1
     For Each c In Sheet1.Range("B11:B500")
            .AddItem c
     Next
End With
End Sub
 
Upvote 0
This will also work:
Code: 
Private Sub UserForm_Initialize()
    ComboBox1.List = Sheets("Sheet8").Range("b11:b500").Value
End Sub
 
Upvote 0
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 ?
 
Upvote 0
Did you try my code? It worked fine for me in both Excel 2003 & 2007.

Sheet Module (as you had posted):
Code: 
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):
Code: 
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?
 
Upvote 0
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.
 
Upvote 0
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.:-?
 
Upvote 0
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 !
 
Upvote 0
I do not have access to Excel 2000 and have never really done any macro/VBA in 2000 but maybe try changing to this.

Code: 
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.
 
Upvote 0
Status
Not open for further replies.

Similar threads

E
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
2
Replies
10
Views
261
Alex Blakenburg
A
E
  • Locked
  • Question Question
VBA - If Cell IsDate and Another Column is a formula then make Cell a hyperlink
Replies
2
Views
114
Fluff
Fluff
K
  • Question Question
VBA Code for User log history
2 3
Replies
21
Views
553
Logit
Logit
D
  • Question Question
VBA to lock Cell after Data Entry but still Allow Sort & Filter ?
Replies
3
Views
296
Jeffrey Mahoney
Jeffrey Mahoney
welshraz
  • Question Question
VBA - Restrict Code to One Column for Multi-choice Dropdown
Replies
2
Views
205
welshraz
welshraz

Forum statistics

Threads
1,225,360
Messages
6,184,508
Members
453,237
Latest member
lordleo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top