asked on

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

StephenJR

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

gtgloner

ASKER

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.

Norie

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.

StephenJR

In one cell? May I ask why?

ASKER CERTIFIED SOLUTION

StephenJR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Norie