Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 188
  • Last Modified:

excel vb macro valuelist multiple values

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
rutgermons
Asked:
rutgermons
  • 3
  • 2
1 Solution
 
David L. HansenProgrammer AnalystCommented:
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
 
rutgermonsAuthor Commented:
yep!
0
 
David L. HansenProgrammer AnalystCommented:
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
 
rutgermonsAuthor Commented:
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
 
David L. HansenProgrammer AnalystCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now