VBA UserForm with Check Box

[jscranton]

I am trying to create a pretty simple UserForm that will contain 4-10 choices that I want end users to select all that apply and then create a text string based on their input. I have browsed the web but nothing seems really clear to me.

Can anyone point me towards resources/step-by-step instructions?

If it matters, this form is going to be displayed by a larger module that will need input from an end user at times. It will not appear on the source excel.

Also, one question I am anticipating is how to display the userform but still allow the source file to be viewed/navigated. In other words, I want to activate the userform but leave the source workbook active as well.

Thanks,

Justin

 

[My Aswer Is This]

My original thought also.

Rather than a bunch of checkboxes, you could use a multiselect ListBox with the .ListStyle set to fmListStyleOption.
Then you could loop through the list box enteries and see what the user checked.

 

[jscranton]

Rather than a bunch of checkboxes, you could use a multiselect ListBox with the .ListStyle set to fmListStyleOption.
Then you could loop through the list box enteries and see what the user checked.

Happy to try. Have a site or thread that walks through the steps?

 

[My Aswer Is This]

If your willing to use a UserForm Listbox.

For this example place a Listbox on your Userform
My script assumes your listbox will be named ListBox1

Then install a button on your Userform and put this script in the button.

Private Sub CommandButton2_Click()
Dim i As Long
Dim ans As String
    For i = ListBox1.ListCount - 1 To 0 Step -1
        If ListBox1.Selected(i) Then
            ans = " " & ListBox1.List(i) & ans
            ListBox1.Selected(i) = False
        End If
    Next
MsgBox ans
End Sub

Now your string will popup in a message box
I did not see what you wanted to do with the string

And put this script in your Userform

It loads values into the Listbox when the userform is Opened
Modify to your needs.

Private Sub UserForm_Initialize()
'Modified  10/21/2019  3:38:13 PM  EDT
ListBox1.MultiSelect = fmMultiSelectMulti
With ListBox1
    .AddItem "George"
    .AddItem "1495 Delta Avenue"
    .AddItem "Atlanta Georgia"
    .AddItem "698789"
End With
End Sub

So select the values in the listbox you want in the string and then press the button with the script.

 

[Fluff]

Happy to try. Have a site or thread that walks through the steps?

Whilst this doesn't include listboxes, it a basic guide to creating a userform https://www.contextures.com/xlUserForm01.html

 

[jscranton]

I have this pretty close to working. So far, I have:

(1) A Userform with a listbox
(2) A Sub UserForm_Initialize that adds my choices to the list
(3) A command button prompt that creates a string object of the items selected (and currently returns them to a message box)

Where I am still struggling is how to call this from the main sub and return the string value

Sub main()
Dim selection as string

'show the user form
UserForm1.Show modeless

'user selects items, hits the cmd button and then

selection = 'string generated by cmd button

End

 

[My Aswer Is This]

You said:

selection = 'string generated by cmd button​

What is selection?

Do you mean active cell.

And your putting this in the initialize script which runs during the open Userform
This would be before you created the string.

 

[mikerickson]

You could do this.
Create a userform with one ListBox (ListBox1) and two command buttons (butOK and butCancel).
Put this code in the userform's code module

Function SelectedValues(ListItems As Variant, Optional Delimiter As String = " ") As String
    Dim oneItem As Variant
    For Each oneItem In ListItems
        Me.ListBox1.AddItem oneItem
    Next oneItem
    Me.Show
    With UserForm1
        If .Tag = "OK" Then
            With .ListBox1
                For oneItem = 0 To .ListCount - 1
                    If .Selected(oneItem) Then
                        SelectedValues = SelectedValues & Delimiter & .List(oneItem)
                    End If
                Next oneItem
                SelectedValues = Mid(SelectedValues, Len(Delimiter) + 1)
            End With
        End If
    End With
End Function

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Tag = "OK"
    Me.Hide
End Sub

Private Sub UserForm_Initialize()
    Rem can be set at design time
    ListBox1.MultiSelect = fmMultiSelectMulti
    ListBox1.ListStyle = fmListStyleOption
End Sub

Then treat it like an InputBox and use it, in some other module, like this.

Dim myVal As String
myVal = UserForm1.SelectedValues(Range("A1:A4"), ",")

If StrPtr(myVal) = 0 Then
    MsgBox "user canceled"
Else
    MsgBox "user selected " & myVal
End If

Note that the ListItems argument of SelectedValues can be either an array or a range. Also note the optional Delimiter argument. Other arguments, like Default, or Title could be added.

 

[jscranton]

Mike,

I think I am getting close here. Two things. My delimiter is always going to be ";" and I don't want to use a range to add to the list. The list is predefined in the userform list box. Show: Show the list box to the user, user selects some items on the list (they will always select at least one), user selects okay, and the function returns to the main a string of the items they have selected.

 

[jscranton]

This is working

Function SelectedValues() As String    Dim oneItem As Variant
    Dim Delimiter As String
    
    Delimiter = ";"
    Me.Show
    With UserForm2
        If .Tag = "OK" Then
            With .ListBox1
                For oneItem = 0 To .ListCount - 1
                    If .Selected(oneItem) Then
                        SelectedValues = SelectedValues & Delimiter & .List(oneItem)
                    End If
                Next oneItem
                SelectedValues = Mid(SelectedValues, Len(Delimiter) + 1)
            End With
        End If
    End With
End Function