object required error using listbox

[Reset]

I am trying to place selected items from a listbox (form control) into an array. I know just enough to be dangerous. The follownig code was lifted directly form a previous posting (ref Andrew Poulsom):

Sub test()

Dim i As Integer
Dim Msg As String
For i = 0 To ListBox7.ListCount - 1
If ListBox7.Selected(i) Then
Msg = Msg & ListBox7.List(i) & vbCrLf
End If
Next i
MsgBox "Selected items are:" & vbCrLf & Msg
End Sub

The only change is it is ListBox7, because I keep trying to redo this. when insert a listbox and go to assign it to a macro it auto fills the macro name as ListBox#_Change. So I am assuming the listbox would be named as such in any code. However, when i run the above code I get "Run-time error '424': Object required".

So, what am I doing wrong? The ultimate goal is to add selected items into an array. Thanks.

 

[Kenneth Hobson]

Please use code tags.

Since you did not put that code in a userform's object standard event, you have to prefix the control's name with the userform's object name, Userform1, typically.

For the array part, use Split().

 

[RoryA]

If you are using a form control, then try:

Sub test()

   Dim i                 As Integer
   Dim Msg               As String
   Dim LB                As ListBox
   Set LB = ActiveSheet.ListBoxes(Application.Caller)
   For i = 1 To LB.ListCount
      If LB.Selected(i) Then
         Msg = Msg & LB.List(i) & vbCrLf
      End If
   Next i
   MsgBox "Selected items are:" & vbCrLf & Msg
End Sub

 

[Reset]

Rory,

With your code provided I have two problems. First my input range is $C:$C, so as is I get an overflow error as the box is including the thousands of blank cells below the last filled cell.

The other problem is that it runs as soon as I choose one entry. I need to be able to make multiple selections then run the code using a button.

 

[RoryA]

Then you'll need to declare i as Long, assign the macro to a button, and refer to the listbox by name, rather than using Application.Caller.
I also would recommend assigning just the data range, not the entire column.

 

[Reset]

Thanks for the input. I went in another direction and got it to work.

For the comment "please use code tags", I don't know how to do that. Tried looking around for instructions but didn't find any.

Thanks again.

 

[RoryA]

See the list of BB tags here.