Macro to Select Multiple Items from Dropdown Checklist

[chain55]

Hello,

I recently found this (www.keithyap.com.au/select-multiple-items-list-excel) online and the GIF shows exactly what I'm looking for. However, the code posted there always generates an error saying invalid object.

I renamed the UserForm and the invalid object error disappears and a UserForm shows up but it's completely blank.

Can someone please take a look and help me point out how I can get it working?

Thank!

 

[chain55]

VBA Code - Selecting Multiple Items from Dropdown Checklist

Hello,

I recently found this (https://www.keithyap.com.au/select-multiple-items-list-excel) online and the GIF shows exactly what I'm looking for. However, the code posted there always generates an error saying "Object Required".

The list that should show up in the userform is A1:A5. I want the userform to popup if I click on C1:C5 then populate in the same cells whichever options I check.

Can someone please take a look and help me point out how I can get it working?

Thank!

Code in Worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
    If Target.Count = 1 Then 'Range of cells to have the pop-up userform
    If Not Intersect(Range("C1:C5"), Target) Is Nothing Then
        UserForm1.Show
    End If
    End If
End Sub

Code in UserForm1

Private Sub UserForm_Initialize()


With ListBox1
 .MultiSelect = fmMultiSelectMulti
 .ListStyle = fmListStyleOption
 plist = Sheets("source ws").Range("A1:A5") 'Range of list
 'Filter Blanks
 For i = LBound(plist) To UBound(plist)
 If plist(i, 1) <> "" Then .AddItem Trim(plist(i, 1))
 Next i
 End With
 End Sub


Private Sub UserForm_Activate()


'Match Listbox selections with ActiveCell selections
 Dim strCell As String, i As Long
 strCell = ActiveCell.Value & MySeperator
 With ListBox1
 For i = 0 To .ListCount - 1
 .Selected(i) = InStr(1, strCell, .List(i), 1)
 Next i
 End With


UserForm1.Caption = "Select Projects"
 CommandButton1.Caption = "OK"


End Sub
 Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 'Hide userform if Close "X" clicked
 If CloseMode = 0 Then
 Cancel = True
 Me.Hide
 End If


End Sub
 Private Sub CommandButton1_Click()


Dim i As Long, strTemp As String
 With ListBox1
 'Read selections
 For i = 0 To .ListCount - 1
 If .Selected(i) Then
 strTemp = strTemp & .List(i) & ","
 .Selected(i) = False ' Clear selections
 End If
 Next i
 End With
 If Len(strTemp) Then
 strTemp = Left(strTemp, Len(strTemp) - Len(MySeperator))
 ActiveCell.Value = strTemp
 Else
 ActiveCell.ClearContents
 ActiveCell.Value = "-- Select Project --"
 End If


UserForm1.Hide


End Sub

 

[John_w]

Re: VBA Code - Selecting Multiple Items from Dropdown Checklist

Cross-posted. Please post links to your threads on other forums.