CODEWITHSUNDEEP
vbaexceldrop-down-menuexcel-2010
udaya726
udaya726

Reputation: 1020

Split values in a dropdown list and display on a spreadsheet

I have a excell sheet with a drop down list. The values are some comma separated values. For example

XXS, XS, S, M, L, XL, XXL, XXXL
2/3, 4/5, 6/7, 8/9, 10/11, 12/13
1-2, 2-3, 3-4, 4-5, 5-6, 6-7, 7-8, 8-9, 9-10, 10-11, 11-12, 12-13, 13-14, 14-15, 15-16, 18-24
2XL, 3XL, 4XL, 5XL
1X, 2X, 3X, 4X, 5X

When user selects a value from the drop down I want to get the selected value and split it by comma and set those splited values to cells. If User selects 2XL, 3XL, 4XL, 5XL my output should look as follows.Can anybody suggests a better to do this

enter image description here

Upvotes: 0

Views: 1754

Answers (1)

user2140173
user2140173

Reputation:

set up your spreadsheet like this

enter image description here

Cell C2 is the validation list dropdown

turn on the developer tab and go into VBE ALT+F11

right click the Sheet1 object module and select View Code

copy paste the below code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Row = 2 And Target.Column = 3 Then
        Dim arr As Variant
        arr = Split(Target, ",")
        Range("E:E").ClearContents
        Range("E:E").NumberFormat = "@"
        Range("E1:E" & UBound(arr) + 1) = WorksheetFunction.Transpose(arr)
    End If
End Sub

Now, go back to spreadsheet and select anything in the list. You should have your separated values in column E

enter image description here

Upvotes: 2

Related Questions