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.

 

[DanAnderton]

Apologies for the double post, I've uploaded a copy of the workbook here: https://1drv.ms/x/s!AlUlWYoo34Yvh_8HvS7ICpyNsDoFHg

 

[Fluff]

Hi & welcome to the board.
One option would be to change your Vales of Yes/No to True/False

 

[Fluff]

Alternatively you could rewrite your event like this

Private Sub ListBox1_AfterUpdate()

    Dim Fnd As Range
    Set Fnd = Sheets("FullRowerDetails").Range("C8:C100").Find(what:=Me.ListBox1.Value)
    
    Me.AddSurname.Value = Fnd.Value
    Me.AddFirstName.Value = Fnd.Offset(, 1).Value
    Me.AddPhone.Value = Fnd.Offset(, 2).Value
    
    
    
    If Fnd.Offset(, 11).Value = "Yes" Then Me.AddFirstAid.Value = True
    If Fnd.Offset(, 12).Value = "Yes" Then Me.AddCoach = True
    
    
    
End Sub

Adding the rest of your boxes

 

[DanAnderton]

Thank's for the quick response.

The worked for getting the right boxes ticked. However switching between names in the listbox changes all the details in the text boxes as expected, but the checkboxs dont update.

 

[Fluff]

For the checkboxes make this change

    If Fnd.Offset(, 11).Value = "Yes" Then Me.AddFirstAid.Value = True Else Me.AddFirstAid.Value = False
    If Fnd.Offset(, 12).Value = "Yes" Then Me.AddCoach = True Else Me.AddCoach = False

Are you ever likely to have 2 or more people with the same surname?

 

[DanAnderton]

I've tried using the code you suggest for the checkboxes, but now the form won't update any of the fields when you select a different name from the listbox.

With the surnames, yes we are. We're a rowing club in North Wales, so as you might expect we've got more than a few people with the surnames Jones, Williams and Evans

 

[Fluff]

Try this

Private Sub ListBox1_AfterUpdate()

    Dim Rw As Long
    
    Rw = ListBox1.ListIndex + 8
    
    Me.AddSurname.Value = Range("C" & Rw).Value
    Me.AddFirstName.Value = Range("D" & Rw).Value
    Me.AddPhone.Value = Range("E" & Rw).Value
    
    Me.AddMobile.Value = Range("F" & Rw).Value
    Me.AddEmail.Value = Range("G" & Rw).Value
    Me.AddAddress.Value = Range("H" & Rw).Value
    Me.AddSex.Value = Range("I" & Rw).Value
    Me.AddDOB.Value = Range("J" & Rw).Value
    Me.AddNOK.Value = Range("L" & Rw).Value
    Me.AddNOKPhone.Value = Range("M" & Rw).Value
    

    If Range("N" & Rw).Value = "Yes" Then Me.AddFirstAid.Value = True Else Me.AddFirstAid.Value = False
    If Range("O" & Rw).Value = "Yes" Then Me.AddCoach = True Else Me.AddCoach = False
    If Range("P" & Rw).Value = "Yes" Then Me.AddRadio = True Else Me.AddRadio = False
    If Range("Q" & Rw).Value = "Yes" Then Me.AddDaySkipper = True Else Me.AddDaySkipper = False
    If Range("R" & Rw).Value = "Yes" Then Me.AddCRB = True Else Me.AddCRB = False
    If Range("S" & Rw).Value = "Yes" Then Me.AddIntroTraining = True Else Me.AddIntroTraining = False
    If Range("T" & Rw).Value = "Yes" Then Me.AddPowerBoat = True Else Me.AddPowerBoat = False
    If Range("U" & Rw).Value = "Yes" Then Me.AddLifejacketTesting = True Else Me.AddLifejacketTesting = False


End Sub

This should deal with multiples of the same name.
Also with the data supplied I'd recommend making the change in red as below

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

 

[DanAnderton]

Just realised why the text boxes weren't updating - seems it was because I am testing it with two rows who both have the same surname, which also explains why you asked.

 

[DanAnderton]

Thank you! It looks like everything is now working as it should. You've been brilliant, thank's for all the help.