Is this Easy?: VBA: Popup Window, select multiple Checkboxes, paste into sheet

[ShazzaBoom]

Hi

I am a newbie to <acronym title="visual basic for applications">VBA</acronym> coding in Excel and this one is just too complex for me to get my head around with userforms and combining checkboxes etc, so reaching out to this great forum I have had so much success with

When you click on cells in column B (not the header row though) I would like to have a popup window appear with Checkboxes where you can select your favourite colours (from the SourceColourList column) by placing a check next to each and then once you have clicked OK it pastes those values to the cell you clicked on in column B.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Colours[/TD]
[TD][/TD]
[TD]SourceColourList[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD][/TD]
[TD][/TD]
[TD]Green[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD][/TD]
[TD][/TD]
[TD]Blue[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD]White[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Pink[/TD]
[/TR]
</tbody>[/TABLE]

Resulting in a sheet that would look like this ...

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Colours[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Red, White, Blue[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Pink[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]Green, Blue, White[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Red, Green[/TD]
[/TR]
</tbody>[/TABLE]

I would like assistance on the process to create the UserForm and then the VBA code to accompany it to achieve the multiple selects and pasting back into the column.

Thanks in advance for any assistance you can provide.

Regards
Shazzaboom

 

[CalcSux78]

For more info on Userforms, check out: Excel UserForm Controls - ComboBox and ListBox: AddItem Method, BoundColumn, List Property, ListIndex, RowSource, Selected Property, ...

So to get started, you'll need an event to trigger the userform call. In VB, doubleclick the sheet you want to setup the trigger.

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("B2:B10")
If Not Intersect(Target, rng) Is Nothing Then
    If Target.Offset(, -1) <> "" Then UserForm1.Show False
End If
End Sub

This will trigger when the user clicks in column B and there is a value in column A.

With the userform, I opted to create a table named "Colours" on sheet 1 containing all of my colour options. This will allow alteration of the colour list without having to re-code the rest of the project later.

Now we need to add a userform to the project. I did a simple userform with a label with the caption "Select all that apply", a listbox and a command button.
With Listbox1, you will need to change the MultiSelect to 1-fmMultiSelectMulti.

Feel free to jazz it up any way you'd like. Use the following for your userform code:

Option Explicit

Private Sub CommandButton1_Click()
Dim i As Integer
Dim str As String
'loop thru all items in listbox to see what is selected.
For i = 0 To ListBox1.ListCount - 1
    If ListBox1.Selected(i) = True Then
        str = str & ", " & ListBox1.List(i)
    End If
Next i
str = Right(str, Len(str) - 2)
ActiveCell.Value = str
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim cel As Range
'setup listbox
With ListBox1
    .Clear
    For Each cel In Range("Colours")
        Me.ListBox1.AddItem cel.Value
    Next cel
End With
End Sub

 

[ShazzaBoom]

This is great - thanks

One thing that I have found in testing is that if I go back to a cell that has colours in it, the UserForm appears but the current data is not autopopulating into the form, as such I have to re-enter all colours again.
In this example it is ok, but if I have a lot of colours it will be difficult to remember them all

 

[CalcSux78]

That's an easy fix. With the Userform_Initialize event, we would need to loop through all of the items in the listbox to see which colors are contained within the cell that triggered the userform.

So replace the old one with this:

Private Sub UserForm_Initialize()Dim cel As Range
[COLOR=#0000cd]Dim sCol As String[/COLOR]
[COLOR=#0000cd]Dim i As Integer[/COLOR]
'setup listbox


[COLOR=#0000cd]sCol = ActiveCell[/COLOR]
With ListBox1
    .Clear
    For Each cel In Range("Colours")
        Me.ListBox1.AddItem cel.Value
    Next cel
End With


[COLOR=#0000cd]For i = 0 To ListBox1.ListCount - 1[/COLOR]
[COLOR=#0000cd]    If InStr(1, sCol, ListBox1.List(i), vbTextCompare) > 0 Then ListBox1.Selected(i) = True[/COLOR]
[COLOR=#0000cd]Next i[/COLOR]
End Sub