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

x
?
Solved

Form selections in one cell

Posted on 2011-10-03
7
Medium Priority
?
200 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:gtgloner
  • 3
  • 2
  • 2
7 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36904495
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
 
LVL 17

Author Comment

by:gtgloner
ID: 36904521
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
 
LVL 36

Expert Comment

by:Norie
ID: 36904529
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 24

Expert Comment

by:StephenJR
ID: 36904530
In one cell? May I ask why?
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 1600 total points
ID: 36904533
Never mind, simpler than I thought.

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

Open in new window

0
 
LVL 36

Assisted Solution

by:Norie
Norie earned 400 total points
ID: 36904539
Oops meant array T and Stephen looks like he's got it anyway.
0
 
LVL 17

Author Closing Comment

by:gtgloner
ID: 36904600
Thank you people for your participation!
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

580 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