Solved

Extended listbox selections to cells

Posted on 2014-07-23
5
155 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 34

Expert Comment

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

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

Industry Leaders: 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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

624 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