Checkboxes iin user form

[DanAnderton]

Hi there,

I'm a complete VBA newbie but have managed to muddle through so far with the project I'm working on.

I've basically created two forms, the first form is to add details to an excel sheet using a combination of text boxes, drop down boxes and check boxes. This form is working exactly as I expected.

I've created a second form that will eventually be used to edit the details previously entered. I'm using a list box to display a list of peoples names who have already been entered, then when you click the name in the list it populates text boxes with the information.

My issue currently is that I can't get it to populate the check boxes correctly and could do with a little advice if possible.

The code for the form to add new details:

Private Sub AddCancel_Click()Unload Me
End Sub


Private Sub AddClear_Click()


Application.ScreenUpdating = False
Unload Me
MYCRowersForm.Show
Application.ScreenUpdating = True


End Sub
Private Sub AddSubmit_Click()
    Dim oNewRow As ListRow
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("FullRowerDetails").Range("FullMembers")
    rng.Select
    Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
    With ws
    oNewRow.Range.Cells(1, 3).Value = Me.AddSurname.Value
    oNewRow.Range.Cells(1, 4).Value = Me.AddFirstName.Value
    oNewRow.Range.Cells(1, 5).Value = Me.AddPhone.Value
    oNewRow.Range.Cells(1, 6).Value = Me.AddMobile.Value
    oNewRow.Range.Cells(1, 7).Value = Me.AddEmail.Value
    oNewRow.Range.Cells(1, 8).Value = Me.AddAddress.Value
    oNewRow.Range.Cells(1, 9).Value = Me.AddSex.Value
    oNewRow.Range.Cells(1, 10).Value = Me.AddDOB.Value
    'Cell 10 left blank for current age.
    oNewRow.Range.Cells(1, 12).Value = Me.AddNOK.Value
    oNewRow.Range.Cells(1, 13).Value = Me.AddNOKPhone.Value


    If AddFirstAid.Value = True Then
    oNewRow.Range.Cells(1, 14).Value = "Yes"
Else
    oNewRow.Range.Cells(1, 14).Value = "No"
End If
        If AddCoach.Value = True Then
    oNewRow.Range.Cells(1, 15).Value = "Yes"
Else
    oNewRow.Range.Cells(1, 15).Value = "No"
End If
        If AddRadio.Value = True Then
    oNewRow.Range.Cells(1, 16).Value = "Yes"
Else
    oNewRow.Range.Cells(1, 16).Value = "No"
End If
        If AddDaySkipper.Value = True Then
    oNewRow.Range.Cells(1, 17).Value = "Yes"
Else
    oNewRow.Range.Cells(1, 17).Value = "No"
End If
        If AddCRB.Value = True Then
    oNewRow.Range.Cells(1, 18).Value = "Yes"
Else
    oNewRow.Range.Cells(1, 18).Value = "No"
End If
        If AddIntroTraining.Value = True Then
    oNewRow.Range.Cells(1, 19).Value = "Yes"
Else
    oNewRow.Range.Cells(1, 19).Value = "No"
End If
        If AddPowerBoat.Value = True Then
    oNewRow.Range.Cells(1, 20).Value = "Yes"
Else
    oNewRow.Range.Cells(1, 20).Value = "No"
End If
        If AddLifejacketTesting.Value = True Then
    oNewRow.Range.Cells(1, 21).Value = "Yes"
Else
    oNewRow.Range.Cells(1, 21).Value = "No"
End If
    End With
    'Clear input controls.
    Me.AddSurname.Value = ""
    Me.AddFirstName.Value = ""
    Me.AddPhone.Value = ""
    Me.AddMobile.Value = ""
    Me.AddEmail.Value = ""
    Me.AddAddress.Value = ""
    Me.AddSex.Value = ""
    Me.AddNOK.Value = ""
    Me.AddNOKPhone.Value = ""


End Sub
Sub UserForm_Initialize()
AddSex.List = Array("Male", "Female")
AddSurname.SetFocus
End Sub

The code so far for the form used to update rows:

Private Sub ListBox1_AfterUpdate()    Me.AddSurname.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 1, False)
    Me.AddFirstName.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 2, False)
    Me.AddPhone.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 3, False)
    Me.AddMobile.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 4, False)
    Me.AddEmail.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 5, False)
    Me.AddAddress.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 6, False)
    Me.AddSex.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 7, False)
    Me.AddDOB.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 8, False)
    Me.AddNOK.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 10, False)
    Me.AddNOKPhone.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 11, False)
    
    
    
    Me.AddFirstAid.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 12, False)
    Me.AddCoach.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 13, False)
    Me.AddRadio.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 14, False)
    Me.AddDaySkipper.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 15, False)
    Me.AddCRB.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 16, False)
    Me.AddIntroTraining.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 17, False)
    Me.AddPowerBoat.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 18, False)
    Me.AddLifejacketTesting.Value = Application.WorksheetFunction.VLookup(Me.ListBox1.Value, Sheets("FullRowerDetails").Range("C8:U100"), 19, False)
    
    
    
End Sub


Private Sub UserForm_Initialize()
ListBox1.ColumnCount = 2                                                 'COLUMN NUMBER OF LISTBOX
ListBox1.List = ThisWorkbook.Worksheets("FullRowerDetails").Range("C8:l" & Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

Sorry if the code is a mess or not efficient, as I said earlier I'm a complete newbie to VBA.

 

[Fluff]

Glad to help & thanks for the feedback