Solved

Extended listbox selections to cells

Posted on 2014-07-23
5
146 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now