Forum Discussion
odiez790
Apr 13, 2024Copper Contributor
Cannot get to edit on my user form to save data
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
Testing.xlsm
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.
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
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
You refer to a variable fa but don't set its value. Because of that, the condition
Loop While Not f Is Nothing And fa <> f.Address
is never met, and the loop would continue endlessly. You have two lines
If Not f Is Nothing Then
in your code. Below each of them, insert
fa = f.Address