Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

excel vb macro valuelist multiple values

Posted on 2014-04-18
5
Medium Priority
?
187 Views
Last Modified: 2014-04-27
folks

I would like my sheet 1 column c be able to choose from a valuelist but allow to select multiple values to the cell , also, would like to have a comma between each value select and no duplications allow i.e

C
car,house,bike
car,house
bike

also, would like the formula /code be applicable to all rows in this column

keen for your guidance
0
Comment
Question by:rutgermons
[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
  • 3
  • 2
5 Comments
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40009329
You want to have a single cell fill with text (multiple values having comma separation) when you select those text values from a list in a control?
0
 

Author Comment

by:rutgermons
ID: 40009360
yep!
0
 
LVL 15

Expert Comment

by:David L. Hansen
ID: 40009748
If you use an ActiveX Listbox (ListBox1) and add the following VBA code to the Sheet it belongs to, you should get the desired results.  Note: I have placed my listbox data in sheet2 column A (rows 1 through 7).  Also, I'm placing the resulting string in cell J7.

Private Sub ListBox1_Change()
Dim selItems As String
    selItems = ""
    
    Cells(7, 10) = "" 'initialize
    
    For i = 0 To Me.ListBox1.ListCount - 1
        If Me.ListBox1.Selected(i) = True Then
            selItems = selItems & Me.ListBox1.List(i) & ", "
        End If
    Next
    
    Cells(7, 10) = selItems
End Sub



Private Sub Worksheet_Activate()
    Me.ListBox1.ListFillRange = "'Sheet2'!A1:A7"
    Cells(7, 10) = ""
End Sub

Open in new window

0
 

Author Comment

by:rutgermons
ID: 40010677
dave

thanks for this, it works but i can't see it add multiple ,selects to the field?

can u share me your sheet perhaps?

cheers
0
 
LVL 15

Accepted Solution

by:
David L. Hansen earned 2000 total points
ID: 40012534
This line: selItems = selItems & Me.ListBox1.List(i) & ", "
concatenates the multiple selections into one string.  If you make your column wider or turn on text wrapping you will see the multiples being displayed.  Also, change your target cell from "Cells(7, 10)" to whatever works best for you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

618 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