adding checkbox to message

[anp6]

Okay, I have looked everywhere and I'm sure it's very easy. I'm writing a program where I have to get alot of input from the user, instead of asking them one by one which parts apply to what they're doing I wanted to put a list of all the parts in a message box and they could check which ones apply. Is this at all possible to do in Excel VBA using code?

[MMcCarthy]

This question is being moved to the Visual basic forum.

ADMIN

[Killer42]

Thanks mmccarthy.

anp6, you will probably want to create a UserForm, in the VBA editor. I'm not experienced in this area, though. Perhaps someone else here can provide more info.

[danp129]

Okay, I have looked everywhere and I'm sure it's very easy. I'm writing a program where I have to get alot of input from the user, instead of asking them one by one which parts apply to what they're doing I wanted to put a list of all the parts in a message box and they could check which ones apply. Is this at all possible to do in Excel VBA using code?

I don't know forms very well but I made this (check for attachment):

'Module code
'Comments:
'Add Userform and name it frmGetParts
'Add Microsoft Listview Control to toolbox and then add as name lvParts
'... Set other properties to: Fullrowselect: True; LabelEdit: Manual; View: LvwReport
 
'Add label named lblMsg
'Add command button called cmdOK, caption: OK
'Double click the OK button and add "Me.Hide" like this
'Private Sub cmdOk_Click()
'    Me.Hide
'End Sub
 
 
Option Explicit
Sub TestMessageBox()
    Dim wksPartsList As Worksheet
    Dim oApplicableParts As Collection
    Dim rngPartNos As Range
    Dim iPart As Long
    'sheet the part numbers are on
    Set wksPartsList = Sheets("PartList")
 
    With wksPartsList
        'range partnumbers are in (in a2:b2)
        Set rngPartNos = .Range("a2:a" & .Range("a65536").End(xlUp).Row)
    End With
 
    'Ask for user to select applicable parts
    Set oApplicableParts = GetApplicableParts("Please select applicable parts:", rngPartNos)
    For iPart = 1 To oApplicableParts.Count
        MsgBox oApplicableParts.Item(iPart)
    Next 'iPart
End Sub
 
Function GetApplicableParts(sMessage As String, rngPartlist As Range) As Collection
    'Function Returns items users check in the listview as a collection
 
    Dim rngPart As Range
    Dim xItem As ListItem
    Dim oSelected As New Collection
 
    With frmGetParts
        'Set message of the window
        .lblMsg = sMessage
        'Add column "Part#" to lvParts
        .lvParts.ColumnHeaders.Add , , "part#"
        'Add column "Name" to lvParts
        .lvParts.ColumnHeaders.Add , , "Name"
        For Each rngPart In rngPartlist
            'Add part# to lvParts
            Set xItem = .lvParts.ListItems.Add(, , rngPart)
            'Add part name to lvParts
            xItem.ListSubItems.Add , , rngPart.Offset(0, 1)
        Next 'rngPart
        'Display the messagebox
        .Show
    End With
 
    For Each xItem In frmGetParts.lvParts.ListItems
        'If item is checked add it to oSelected collection
        If xItem.Checked = True Then oSelected.Add xItem.Text
    Next 'xItem
 
    'Set this function to oSelected collection (thus making it return a collection of selected parts)
    Set GetApplicableParts = oSelected
    Set oSelected = Nothing
    'Unload message window
    Unload frmGetParts
End Function