Return Multiple Listbox Selection

[cpounds217]

Hello Everybody,

First, please don’t ask for me to upload my document, I can’t, its work related and I am not allowed.

Second, I don’t any code established yet because I cannot seem to find the code I am looking for anywhere, hence me coming here.

Ok now:

I am trying to return a multiple item selection in a ListBox to multiple cells in Excel.

Example:

Listbox Selection: X, Y, Z

Returns
Column A
1. Header
2. X
3. Y
4. Z
5.

I have tried multiple searches asking for return to column, return as array, yet nothing seems to give me an approach by code that seems to work or return what I am looking for.

Any hints and/or help you can provide would be greatly appreciated!

Thanks!
Chris

[paulked]

    Sheet1.Cells(1, 1) = "ListBox1 Selected Items"
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then Sheet1.Cells(i + 2, 1) = ListBox1.List(i)
    Next

[p45cal]

First, please don’t ask for me to upload my document, I can’t, its work related and I am not allowed.

Could you upload your document? All right then, a document? Make one from scratch, with a listbox and button, some dummy data to fill the listbox etc. It will answer many questions at a stroke: Where is the listbox? (On a sheet or a userform.), What kind of listbox is it (ActiveX or not)? Does it have multiple columns, which column(s) do you want to return to the sheet. What/Where is the button or mechanism to start the code running?
If you're not allowed to upload anything at all from work, do it from home.
It will benefit you with a quick and accurate answer without our guessing (wrongly) what your setup is, and we won't be left wondering what your setup is like, and a big plus for us, less work to do setting up such a document so we're more inclined to give an answer at all!

[SamT]

Paulked's code refactored

Dim Rw as  long
Dim i As Long

Rw = 1
Sheet1.Cells(Rw, "A") = "ListBox1 Selected Items" 
Rw = 2

For i = 0 To ListBox1.ListCount - 1 
    If ListBox1.Selected(i) = True Then Sheet1.Cells(Rw, "A") = ListBox1.List(i) 
Rw = Rw + 1
Next

[Paul_Hossler]

I think the other suggestions assume that the selected items start at the beginning and are contiguous

This might be a little more flexible since it uses an output row counter that get incremented only when a selected list box item is written to the sheet

Dim idxListbox As Long, idxCell As Long

idxCell = 1

Sheet1.Cells(1, idxCell) = "ListBox1 Selected Items"

For idxListbox = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(idxListbox) Then
        idxCell = idxCell + 1
        Sheet1.Cells(i, idxCell) = ListBox1.List(idxListbox)
    End If
Next

[cpounds217]

Thanks all!

Now I am trying to return Yes or No for a Checkbox, but only if the checkbox is enabled.

The Checkbox default is enabled unless the caption of the label for that row of questions is blank:

If xyzLabel.Caption = “” then
Checkbox1.Enabled = False
Elseif xyzLabel.Caption <> “” then
Checkbox1.Enabled = True
End if

That is called in the Userform_Initialize event.

Currently I have a the following function (ps: I have 25 checkboxes that all have to run and a total of around 250 controls that return values upon clicking enter):

Private Function YesOrNo (BoxVal as Boolean) As String

Dim ctl As Object

For each ctl in Me.Controls
if typeof ctl is MsForms.Combobox and ctl.enabled = true then
If ctl = true then
YesOrNo = “Yes”
Elseif ctl = false then
YesOrNo = “No”
End if
End if
next

End function

This function is then called in similar fashion for all checkboxes:

Private Function MoveValues ()

wsAnalysis.Range(“B2”) = YesOrNo (Impact1)
etc...

End Function

And of course MoveValues is called into the Enter Button sub.

I currently have it so it stops telling me invalid use of Null which was happening when I tried Triple State. But now I get no value returned at all.

Please help!

[SamT]

The YesNo Function will always reflect the state of the last ComboBox in the Form's Collection of Controls.

Further, since a ComboBox can not be True, YesNo will always be "No".
OR:
Since a ComboBox can not be false, YesNo will be "Yes" any time that ComboBox is enabled, which is always.

Regardless, It is a GIGO Function.

Private Function YesOrNo(Ctl As Object) As String
YesOrNo = "No"
if Ctl = True and Ctl.Enabled = True then YesOrNo = "Yes"
End Function

[cpounds217]

Ok, but this will return Unchecked, Disabled checkboxes as “No” and not as blank, correct?

I am looking for:
Checkbox = True = Yes
Checkbox = False = No
Checkbox = Disabled = “ “

[SamT]

Correct.

Private Function YesOrNo(Ctl As Object) As String 
'Returns "Yes", "No", or ""

If not Ctl.Enabled then Exit Function

YesOrNo = "No"

If Ctl = True Then YesOrNo = "Yes" 
End Function

[cpounds217]

Thank you! That helped immensely.

[cpounds217]

I now have my values populated as I want them. Now I need those values to compare to two lists (per value) that house the value and a corresponding numerical value. Then all ten numerical values are added for a score. I am using a formula, but I am having difficulty with creating just the right formula for this. I know I have used it before something similar before, but that was when the lists and lookup value were on the same worksheet, currently (and preferably) they are not on the same worksheet.

Below is the formula I have tried, but it continually returns a “There’s a problem with this formula” message, which doesn’t help because when I click ‘OK’, it highlights the entire formula as the problem. As you can see I am testing whether the first value in the row is “Yes”, and then adding the assigned numerical values that correspond with the text values for the score. The numerical lists are housed in xyzV (after INDEX function) and the text values are housed in xyz.

I am really lost on what I am doing wrong here, and I have tried numerous google searches on what formulas to use, how to set it up, and nothing seems to return a valid or helpful answer.

=If(B2="Yes",(SUM(INDEX(OccurV,MATCH(C2,Occur,0))),(INDEX(AnotherV,MATCH(D2 ,Another,0))),(INDEX(FreqV,MATCH(E2,Freq,0))),(INDEX(PopulationV,MATCH(F2,P opulation,0))),(INDEX(PrivateV,MATCH(G2,Private,0))),(INDEX(InfraV,MATCH(H2 ,Infra,0))),(INDEX(WarningV,MATCH(I2,Warning,0))),(INDEX(DurationV,MATCH(J2 ,Duration,0))),(INDEX(OperationsV,MATCH(K2,Operations,0)))),0)

Thanks!

[SamT]

MATCH(F2,P opulation,0)
Typo?

[cpounds217]

Oh, I see that now. But I ended up working with VLOOKUP(......)+VLOOKUP(....) etc, and just hid the arrays on the same worksheet.

Thanks for the help though!