Cannot get to edit on my user form with 1 to 5 members on it

[hendrikbez]

I have a user form that I can choose a surname, then it displays up to 5 members of family if there are 5, but I cannot get it to save when I press the Edit and save button.
Getting error (when I open or choose a surname) Subscript out of range memberRowIndices(memberIndex + 2) = f.Row ' Store the row index for this member

I just cannot get it to work.
It must let me edit any member data, and save it to the correct row where it must be.

This is my choose surname edit code

Private Sub cboSurname_Change()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim surname As String
    Dim surnameRows() As Long
    Dim surnameRowIndex As Long
    Dim memberIndex As Long
    Dim selectedRowIndex As Long
    Dim memberRowIndices() As Long
    
    cboSurNameID.ListIndex = cboSurname.ListIndex
    
    Set ws = ThisWorkbook.Sheets("Register")
    
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    
    surname = Me.cboSurname.Value
    surnameid = Me.cboSurNameID.Value
    
    'Clear the existing data in the controls
    ClearControlValues
    
    'Find all the rows with the selected surname
    With ws.Range("A10:A" & lastRow)
        Set f = .Find(whaT:=surnameid, lookat:=xlWhole)
        If Not f Is Nothing Then
            ' Store the selected row index
            selectedRowIndex = f.Row - 9 ' Assuming the data starts from row 10
            
            ReDim memberRowIndices(2 To 5) As Long
            
            memberIndex = 0
            Do
                Set ff = ws.Range("C" & f.Row)
                
                Me.Controls("txtNaam" & memberIndex + 1).Value = ff.Offset(0, 1).Value
                Me.Controls("txtverjaar" & memberIndex + 1).Value = Format(ff.Offset(memberIndex, 2).Value, "dd mmm")
                Me.Controls("txtselfoon" & memberIndex + 1).Value = ff.Offset(0, 6).Value
                memberRowIndices(memberIndex + 2) = f.Row ' Store the row index for this member
                
                i = 1
                memberIndex = memberIndex + 1
                
                While f.Offset(i, 0) = "" And memberIndex < 5
                    If f.Offset(i, 1).Value <> "" Then
                        Me.Controls("txtNaam" & memberIndex + 1).Value = ff.Offset(i, 1).Value
                        Me.Controls("txtverjaar" & memberIndex + 1).Value = Format(ff.Offset(i, 2).Value, "dd mmm")
                        Me.Controls("txtselfoon" & memberIndex + 1).Value = ff.Offset(i, 6).Value
                        memberRowIndices(memberIndex + 2) = f.Offset(i, 0).Row ' Store the row index for this member
                        memberIndex = memberIndex + 1
                    End If
                    i = i + 1
                Wend
                
                Me.txtVan.Value = ff.Offset(0, 0).Value
                Me.txthuwelikdatum.Value = Format(ff.Offset(0, 3).Value, "dd mmm")
                Me.txtHuis.Value = ff.Offset(0, 4).Value
                Me.txtWerk.Value = ff.Offset(0, 5).Value
                Me.txtadres.Value = ff.Offset(0, 7).Value
                
                Set f = .FindNext(f)
            Loop While Not f Is Nothing And fa <> f.Address
        Else
            ' Reset the form
            ClearControlValues
            selectedRowIndex = -1
        End If
    End With
    
    ' Pass the member row indices to the btnEdit_Click() subroutine
    Call btnEdit_Click
End Sub

and this is my edit code

Private Sub btnEdit_Click()
    Dim ws As Worksheet
    Dim rowIndex As Long
    Dim memberIndex As Long
    Dim memberRowIndex As Long ' Change this line
    
    On Error GoTo ErrorHandler
    
    Set ws = ThisWorkbook.Sheets("Register") ' Change "Register" to the name of your sheet
    
    ' Check if a row is selected for editing
    If selectedRowIndex >= 0 And selectedRowIndex <= ws.Range("A" & Rows.Count).End(xlUp).Row Then
        ' Write data to the sheet
        With ws
            ' Update the fields for the main member
            .Cells(selectedRowIndex + 1, 3).Value = Me.txtVan.Value ' Value from txtVan textbox
            .Cells(selectedRowIndex + 1, 4).Value = Me.txtNaam1.Value ' Value from txtNaam1 textbox
            .Cells(selectedRowIndex + 1, 5).Value = CDate(Me.txtverjaar1.Value) ' Value from txtverjaar1 textbox
            .Cells(selectedRowIndex + 1, 11).Value = Me.txtselfoon1.Value ' Value from txtselfoon1 textbox
            
            ' Get the row index for the additional member
            memberRowIndex = GetMemberRowIndex(2) ' Change here
            
            ' Update the additional member
            If memberRowIndex > 0 And memberRowIndex <= ws.Range("A" & Rows.Count).End(xlUp).Row Then ' Change here
                .Cells(memberRowIndex, 4).Value = Me.txtNaam2.Value
                .Cells(memberRowIndex, 5).Value = CDate(Me.txtverjaar2.Value)
                .Cells(memberRowIndex, 11).Value = Me.txtselfoon2.Value
            End If
            
            ' Update the other fields
            .Cells(selectedRowIndex + 1, 12).Value = Me.txthuwelikdatum.Value ' Value from txthuwelikdatum textbox
            .Cells(selectedRowIndex + 1, 13).Value = Me.txtHuis.Value ' Value from txtHuis textbox
            .Cells(selectedRowIndex + 1, 14).Value = Me.txtWerk.Value ' Value from txtWerk textbox
            .Cells(selectedRowIndex + 1, 16).Value = Me.txtadres.Value ' Value from txtadres textbox
        End With
        
        ' Clear input fields after saving
        Me.txtVan.Value = ""
        Me.txthuwelikdatum.Value = ""
        Me.txtHuis.Value = ""
        Me.txtWerk.Value = ""
        Me.txtadres.Value = ""
        
        ' Clear additional member fields
        Me.txtNaam2.Value = ""
        Me.txtverjaar2.Value = ""
        Me.txtselfoon2.Value = ""
        
    Else
        MsgBox "Please select a record to edit."
    End If
    
    Exit Sub
    
ErrorHandler:
    MsgBox "An error occurred: " & Err.Description
End Sub

 

[Anthony47]

You Dim memberRowIndices(2 To 5) i.e. an array with 4 entries but then try to load in it 5 values; probably your redim should state (2 to 6)