Solved

Extended listbox selections to cells

Posted on 2014-07-23
5
152 Views
Last Modified: 2014-07-24
Hi Experts,

I have a list box (Countrylst) that has names of countries in it. I need to be able to select Multiple countries so have set its selection type to 'extended' (it is on a worksheet - 'Location' in an excel file).

I need to get the countries that have been selected into a cell so that I can use the country names elsewhere in the workbook. Obviously when I have the selection type set to 'single' I can get a number reference for the selection (eg cell N1 returns Cambodia if it has been selected). How do I get the results when the listbox is extended?

I'm sure this is possible but not sure where the code needs to go or what it needs to do...

Thanks in advance for your help!
0
Comment
Question by:martywal
  • 2
  • 2
5 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 40216125
You say you want the selections in a cell, how would you see it formated?
Would they be comma seperated?
With all in one cell would they be useful, or would them being in all cells from a set cell down be better?
0
 
LVL 33

Expert Comment

by:Norie
ID: 40216311
Which type of listbox is it, Forms or ActiveX?
0
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 40216324
This code is for a control from the Forms toolbox.
Sub GetAllSelectedFromFormsListBox()
Dim lst As Object
Dim I As Long
Dim arrSelected()
Dim cnt As Long
Dim Delim As String

    Delim = ","

    Set lst = [Countrylst]

    For I = 1 To [Countrylst].ListCount
        If [Countrylst].Selected(I) Then
            ReDim Preserve arrSelected(cnt)

            arrSelected(cnt) = [Countrylst].List(I)

            cnt = cnt + 1
        End If
    Next I

    If cnt > 0 Then

        ' put in cell as delimited list
        Range("B1").Value = Join(arrSelected, Delim)

        ' put in row
        Range("D1").Resize(, UBound(arrSelected) + 1).Value = arrSelected

        ' put in column
        Range("B3").Resize(UBound(arrSelected) + 1).Value = Application.Transpose(arrSelected)
        
    End If

End Sub

Open in new window


The code for an ActiveX listbox will be similar, with some changes in the loop.
0
 

Author Comment

by:martywal
ID: 40218300
That's great thanks so much for your help on this one!!!
0
 

Author Closing Comment

by:martywal
ID: 40218301
Quick and accurate response.
Gotta love Experts Exchange!!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question