List from text.

[Botje]

Since i cannot upload images ( im limited @ my work ) i try to explain as good as i can.

I got a form. In this form i got 12 check boxes. If a person marks a checkbox the answer will be shown @ line "AP#-BB#" which makes the following code:

If Me.chkAchmeaVitale.Value = True Then
.Offset(RowCount, 40).Value = "Achmea Vitale"
Else
.Offset(RowCount, 40).Value = ""
End If
If Me.chkKeuringen.Value = True Then
.Offset(RowCount, 41).Value = "Keuringen"
Else
.Offset(RowCount, 41).Value = ""
End If
If Me.chkKlantenservice.Value = True Then
.Offset(RowCount, 42).Value = "Klantenservice"
Else
.Offset(RowCount, 42).Value = ""
End If
If Me.chkFrontoffice.Value = True Then
.Offset(RowCount, 43).Value = "Frontoffice"
Else
.Offset(RowCount, 43).Value = ""
End If
If Me.chkExoten.Value = True Then
.Offset(RowCount, 44).Value = "Exoten"
Else
.Offset(RowCount, 44).Value = ""
End If
If Me.chkMKB.Value = True Then
.Offset(RowCount, 45).Value = "MO MKB"
Else
.Offset(RowCount, 45).Value = ""
End If
If Me.chkDAM.Value = True Then
.Offset(RowCount, 46).Value = "DAM"
Else
.Offset(RowCount, 46).Value = ""
End If
If Me.chkBA.Value = True Then
.Offset(RowCount, 47).Value = "Bedrijfsartsen en Consulenten"
Else
.Offset(RowCount, 47).Value = ""
End If
If Me.chkRAM.Value = True Then
.Offset(RowCount, 48).Value = "RAM"
Else
.Offset(RowCount, 48).Value = ""
End If
If Me.chkSAM.Value = True Then
.Offset(RowCount, 49).Value = "SAM"
Else
.Offset(RowCount, 49).Value = ""
End If
If Me.chkAMI.Value = True Then
.Offset(RowCount, 50).Value = "MO AMI"
Else
.Offset(RowCount, 50).Value = ""
End If
If Me.chkGMAT5.Value = True Then
.Offset(RowCount, 51).Value = "MO GM AT5"
Else
.Offset(RowCount, 51).Value = ""
End If
If Me.chkICO.Value = True Then
.Offset(RowCount, 52).Value = "Interventie Coördinatoren"
Else
.Offset(RowCount, 52).Value = ""

I want to make a list of the answers that are checked.
But when I make a list I need to make a new list for every row.

I want to make a formula/code that i can add to my VBA code so it automaticly sums up all the answer from the checkboxes at "C5".

I tried with the

1. VLOOKUP
2. Through "Data->Validation" But i got to do this step every single row.
3. Dim Totallist; Dim Cell As Range

4. Private Sub SpinButton1_Change() End Sub

This is my entire code:

Private Sub cmdinvullen_Click()
Dim RowCount As Long
    If Me.TxtNaam.Value = "" Then
    MsgBox "Vul alsjeblieft je naam in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.TxtNaam.SetFocus
    Exit Sub
End If
    If Me.txtidee.Value = "" Then
    MsgBox "Vul alsjeblieft je idee in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.txtidee.SetFocus
    Exit Sub
End If
 If Me.ComboBox1.Value = "" Then
    MsgBox "Vul alsjeblieft je team in.", vbExclamation, "Invulformulier Ideeën Bord"
    Me.ComboBox1.SetFocus
    Exit Sub
End If
RowCount = Worksheets("Archief").Range("B1").CurrentRegion.Rows.Count
With Worksheets("Archief").Range("B1")
.Offset(RowCount, 2).Value = Me.TxtNaam.Value
.Offset(RowCount, 0).Value = Me.txtidee.Value
If Me.chkTijd.Value = True Then
.Offset(RowCount, 30).Value = "Tijd"
Else
.Offset(RowCount, 30).Value = ""
End If
If Me.chkGeld.Value = True Then
.Offset(RowCount, 31).Value = "Geld"
Else
.Offset(RowCount, 31).Value = ""
End If
If Me.chkCollegas.Value = True Then
.Offset(RowCount, 32).Value = "Collega's"
Else
.Offset(RowCount, 32).Value = ""
End If
If Me.chkToestemming.Value = True Then
.Offset(RowCount, 33).Value = "Toestemming"
Else
.Offset(RowCount, 33).Value = ""
End If
If Me.chkRuimte.Value = True Then
.Offset(RowCount, 34).Value = "Ruimte"
Else
.Offset(RowCount, 34).Value = ""
End If
If Me.chkAnders.Value = True Then
.Offset(RowCount, 35).Value = "Anders"
Else
.Offset(RowCount, 36).Value = ""
End If
If Me.chkAchmeaVitale.Value = True Then
.Offset(RowCount, 40).Value = "Achmea Vitale"
Else
.Offset(RowCount, 40).Value = ""
End If
If Me.chkKeuringen.Value = True Then
.Offset(RowCount, 41).Value = "Keuringen"
Else
.Offset(RowCount, 41).Value = ""
End If
If Me.chkKlantenservice.Value = True Then
.Offset(RowCount, 42).Value = "Klantenservice"
Else
.Offset(RowCount, 42).Value = ""
End If
If Me.chkFrontoffice.Value = True Then
.Offset(RowCount, 43).Value = "Frontoffice"
Else
.Offset(RowCount, 43).Value = ""
End If
If Me.chkExoten.Value = True Then
.Offset(RowCount, 44).Value = "Exoten"
Else
.Offset(RowCount, 44).Value = ""
End If
If Me.chkMKB.Value = True Then
.Offset(RowCount, 45).Value = "MO MKB"
Else
.Offset(RowCount, 45).Value = ""
End If
If Me.chkDAM.Value = True Then
.Offset(RowCount, 46).Value = "DAM"
Else
.Offset(RowCount, 46).Value = ""
End If
If Me.chkBA.Value = True Then
.Offset(RowCount, 47).Value = "Bedrijfsartsen en Consulenten"
Else
.Offset(RowCount, 47).Value = ""
End If
If Me.chkRAM.Value = True Then
.Offset(RowCount, 48).Value = "RAM"
Else
.Offset(RowCount, 48).Value = ""
End If
If Me.chkSAM.Value = True Then
.Offset(RowCount, 49).Value = "SAM"
Else
.Offset(RowCount, 49).Value = ""
End If
If Me.chkAMI.Value = True Then
.Offset(RowCount, 50).Value = "MO AMI"
Else
.Offset(RowCount, 50).Value = ""
End If
If Me.chkGMAT5.Value = True Then
.Offset(RowCount, 51).Value = "MO GM AT5"
Else
.Offset(RowCount, 51).Value = ""
End If
If Me.chkICO.Value = True Then
.Offset(RowCount, 52).Value = "Interventie Coördinatoren"
Else
.Offset(RowCount, 52).Value = ""
End If
.Offset(RowCount, 3).Value = Format(Now, "dd/mm/yyyy hh:nn:ss")
End With
    Unload Me
End Sub
Private Sub cmdsluiten_Click()
    Unload Me
End Sub

Thanks in advance.

 

[Botje]

With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="Range(Cells(.Row, "AF"), Cells(.Row, "AK")).Select
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

This is what i got now but it aint working? What mistake am i making here?