Form selections in one cell

I have a form in a sheet where multiple checkboxes can be selected for text strings that are then copied and inserted in a column of cells. Open the attached file, select the "test" sheet and click "Run Macro". Select some checkboxes and click "Commandbutton1" then "Commandbutton2" and view the "Save" sheet and you will see your selections listed in the A column. Now, what I am trying to do is to have these selections all listed in the A1 cell, not one selection in each cell. Hope this is clear.
ZMulit-Selections---Play-Here.xls
LVL 17
gtglonerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StephenJRCommented:
Private Sub CommandButton1_Click()
Dim T()
Dim i&
Dim cpt&
Dim S As Worksheet
With ListBox1
  For i& = 0 To .ListCount - 1
    If .Selected(i&) Then
      cpt& = cpt& + 1
      ReDim Preserve T(1 To cpt&)
      T(cpt&) = .List(i&)
      .Selected(i&) = False
    End If
  Next i&
End With
If cpt& > 0 Then
  Set S = Sheets(SHEET_SAVE)
  S.Cells.Delete
  S.Range("A1") = Join(T, ",")
  'S.Range("a1:a" & UBound(T, 2) & "") = Application.WorksheetFunction.Transpose(T)
End If
End Sub

Open in new window

0
gtglonerAuthor Commented:
Hi StephenJR, thanks for the response. You're close, but I need the selections listed down, eg. instead of 2,4,6,7 in cell A1:

2
4
6
7

in cell A1.
0
NorieVBA ExpertCommented:
If your array Y was one dimensional you could use Join to concatenate it's values.

You could easily do that just by removing the first dimension.
Private Sub CommandButton1_Click()
Dim T()
Dim i&
Dim cpt&
Dim S As Worksheet
    With ListBox1
        For i& = 0 To .ListCount - 1
            If .Selected(i&) Then
                cpt& = cpt& + 1
                ReDim Preserve T(1 To cpt&)
                T(cpt&) = .List(i&)
                .Selected(i&) = False
            End If
        Next i&
    End With

    If cpt& > 0 Then
        Set S = Sheets(SHEET_SAVE)
        S.Cells.Delete
'       S.Range("a1:a" & UBound(T, 1) & "") = Application.WorksheetFunction.Transpose(T)
       S.Range("A1") = Join(T, "")
    End If
End Sub

Open in new window

By the way, you do realise the userform's initialize event will never execute, UserForm1 should just be UserForm.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

StephenJRCommented:
In one cell? May I ask why?
0
StephenJRCommented:
Never mind, simpler than I thought.

S.Range("A1") = Join(T, Chr(10))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieVBA ExpertCommented:
Oops meant array T and Stephen looks like he's got it anyway.
0
gtglonerAuthor Commented:
Thank you people for your participation!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.