Help troubleshooting ListBox additem

[AnswersAreEasy]

Hi, I've only been working with VBA for three hours so be a little patient.

I have a listbox on my worksheet "Main", and I want the options on this to update from a variable list (N3 - N*).
This is what I have right now for the code:

---

Private Sub ListBox10_Change()
Dim SeamRa As Object
Set SeamRa = Range("N3")
If Len(SeamRa.Formula) = 0 Then
Call SeamSort
Else
If Len(SeamRa.Formula) > 0 Then
.ListBox10.AddItem.SeamRa
SeamRa = SeamRa.Offset(1, 0)
End If
End If
End Sub

---

I'm getting an error that the Sub line is "Invalid or unqualified",
where I introduce the SeamRa object it says as an error "object rRange is undefined", even though I have no rRange object. And finally, where I add the SeamRa item to my list box, it says ListBox10 is undefined.
Additionally, this code is located on Module1. The listbox and range source are on the same sheet, but eventually I want to move the range source over to a different worksheet.

So far my coding has been going pretty well but I am completely stumped on this one.
Thanks you guys!

 

[AnswersAreEasy]

Of course the moment I post it I realize a flaw in the code. This is the updated code with a loop:

Private Sub ListBox10_Change()
Dim SeamRa As Object
Set SeamRa = Range("N3")
If Len(SeamRa.Formula) = 0 Then
Call SeamSort
Else
Do Until Len(SeamRa.Formula) = 0
.ListBox10.AddItem.SeamRa
SeamRa = SeamRa.Offset(1, 0)
Loop
End If
End If
End Sub

I'm still getting the same errors though....

 

[Norie]

It should just be this.

.ListBox10.AddItem  SeamRa

 

[AnswersAreEasy]

Thanks!

Unfortunately still getting the "Invalid or unqualified reference" on the AddItem line.

 

[Norie]

You need a worksheet reference for the listbox.

 

[AnswersAreEasy]

Entered the line as this, with the worksheet reference

Worksheets("Main").Shapes("ListBox10").AddItem SeamRa

or as just

Worksheets("Main").ListBox10.AddItem SeamRa

and still getting the same reference error for the ListBox10 object. I know I'm using the correct listbox name too.

 

[Norie]

How did you create the listbox?

 

[AnswersAreEasy]

Inserted it as a Listbox form into an excel cell.

I've changed up the code a bit now, now I call a sub PopulateList from another sub. I do this with:

   Worksheets("Main").Shapes("ListBox10").OnAction = "PopulateList"

then the code runs, which I have now edited to be:

Sub PopulateList()
Dim SeamRa As Object
Set SeamRa = Range("N3")
If Len(SeamRa.Formula) = 0 Then
Call SeamSort
Else
Do Until Len(SeamRa.Formula) = 0
Worksheets("Main").Shapes("ListBox10").AddItem SeamRa
SeamRa = SeamRa.Offset(1, 0)
Loop
End If
End Sub
[/sub]
Sorry for changing it up so much here, I'm just trying to find an answer through google too.

 

[Norie]

Did you create it from the ActiveX or the Forms toolbar?

 

[AnswersAreEasy]

Forms, not activex