Cant Modify Data with User Form - Help with code!

Hello eddyvilla,

Bienvenido a Forum!

The reason the UserForm would not update a record was the ComboBox loaded the records using the RowSource property. This make the ComboBox data read only. Any changes you make to the ComboBox are overridden by the RowSource range data. The code below is all the code for the UserForm "frmEditData".

UserForm_Initialize Event Code

Dim bEdit As Boolean
Dim RecordRow As Long

Private Sub cmdCancel_Click()
'Unload the userform
    Unload Me
End Sub

Private Sub cmdDelete_Click()
        
        Sheet1.Rows(RecordRow).EntireRow.Delete
        bEdit = False
    
End Sub

Private Sub cmdOkay_Click()
        
        With Worksheets("Sheet1").Rows(RecordRow).Resize(1, 7)
            .Cells(1, 1) = txtfname.Text
            .Cells(1, 2) = txtlname.Text
            .Cells(1, 3) = txtloan.Text
            .Cells(1, 4) = txtporescan.Text
            .Cells(1, 5) = DateValue(txtopen.Text)
            .Cells(1, 6) = DateValue(txtclose.Text)
            .Cells(1, 7) = txtsb.Text
        End With
 
        Worksheets("INPUT").Range("E5").Select
 
End Sub

Private Sub ComboBox1_Click()

    Dim vreg As String, drow As Range, c As Range
    
        If bEdit Then Exit Sub
        
        vreg = ComboBox1.Value
        
            With Sheets("sheet1").Range("A2:A100")
                Set c = .Find(vreg, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)
                If Not c Is Nothing Then
                    Set drow = c.Resize(1, 7)
                    RecordRow = drow.Row
                Else
                    Exit Sub
                End If
            End With

            txtfname.Value = drow.Cells(1, 1)
            txtlname.Value = drow.Cells(1, 2)
            txtloan.Value = drow.Cells(1, 3)
            txtporescan.Value = drow.Cells(1, 4)
            txtopen.Value = drow.Cells(1, 5)
            txtclose.Value = drow.Cells(1, 6)
            txtsb.Value = drow.Cells(1, 7)

End Sub


Private Sub UserForm_Initialize()

    Dim LastRow As Long
    Dim Rng As Range
    
        Set Rng = Range("A2:G2")
        LastRow = Cells.Find("*", , xlFormulas, xlWhole, xlByRows, xlPrevious).Row
        
        Set Rng = IIf(LastRow < 2, Rng, Rng.Resize(RowSize:=LastRow - Rng.Row + 1))
        
        frmEditData.ComboBox1.List = Rng.Columns(1).Cells.Value

End Sub

Hello Eddy,

Where in the code does the error occur?

Hello Eddy,

This is quite strange. When I call up your program that I saved with the changes, it gives me the same error. Earlier, it was working fine.

In set RNG on INITIALIZE.. the IF has 2 "II"... nevermind... no clue men!

Thanks!

Hello Eddy,

The code as I wrote it will find the last row with data in the column "A" starting in row 2. Are you saying if there are any blanks in that range to exclude them?

Hello Eddy,

De nada. Gusto que te puede ayudar.