Excel List Box Output

[geekette]

Hi,

I have created a list box using the Control Toolbox. It's Listfill range is looking up a named range I have created called LOOKUP_Skills. I have set it to Multi select items.

What I now need to do is output any items selected in the list to another range in my workbook so I can then run a query based on these selections. I have been reading lots of online advice but all seem to point to list boxes that have been created on user forms.

Any help would be very happily received :O)

 

[geekette]

I've removed that but now get an error message at this part of the code:

Set DestCell = .Range("G" & inextrow)

"Application Defined or Object Defined Error"

?

 

[Andrew Poulsom]

Where have you declared and assigned a value to inextrow?

 

[geekette]

I'm sorry I'm not sure.....(I'm not best at vba :O( )

I copied the code and the only parts I have amended were the reference to sheetname and list box all other code I left untouched so I'm not sure how I would/should declare anything within the code?

 

[Andrew Poulsom]

Where did you copy the code from?

 

[geekette]

I found it at the following link

http://www.eggheadcafe.com/software/aspnet/35649453/saving-values-from-multiselect-list-box.aspx

 

[Andrew Poulsom]

Try:

Private Sub ListBox1_Change()
    Dim inextrow As Long
    Dim DestCell As Range
    Dim iCtr As Long
    inextrow = 2
    With Sheets("View My Requests")
        Set DestCell = .Range("G" & inextrow)
    End With
    With Me.ListBox1
        DestCell.Resize(.ListCount, 1).ClearContents
        For iCtr = 0 To .ListCount - 1
            If .Selected(iCtr) Then
                DestCell.Value = .List(iCtr)
                Set DestCell = DestCell.Offset(0, 1)
            End If
        Next iCtr
    End With
End Sub

 

[geekette]

Thank you so much - that's now doing exactly as required with no errors.... thanks!