conflict between save and selecting from listbox

[Eternallamp]

In my Excel user form, the option to click on a row in the list box and return the selected entry into my data entry fields. After making changes and attempting to save, no updates are made. If i comment out the click event to select a row from the list box, then the Save command works fine. Note, my data starts in the fourth row of the worksheet. So it seems, the save event is triggering the selected event and my updates disappear. Do appreciate your insights into this.

Code to select and return selected into form for editing:
Private Sub lstLoanRates_Click()
Dim SelectedRec As Integer
For i = 0 To lstLoanRates.ListCount - 1
If lstLoanRates.Selected(i) = True Then
txtRefNo.Value = Worksheets("LoanRates").Cells(i + 4, 1).Value
txtLoanNo.Value = Worksheets("LoanRates").Cells(i + 4, 4).Value
txtProgId.Value = Worksheets("LoanRates").Cells(i + 4, 6).Value
txtIntRate.Value = Worksheets("LoanRates").Cells(i + 4, 8).Value
txtDisbDate.Value = Worksheets("LoanRates").Cells(i + 4, 9).Value
End If
Next i
End Sub

Code to save.
Private Sub cmdSave_Click()
i = txtRefNo.Value + 3
Worksheets("LoanRates").Cells(i, 1).Value = txtRefNo.Value 'Then
Worksheets("LoanRates").Cells(i, 8).Value = Me.txtIntRate.Value
Worksheets("LoanRates").Cells(i, 9).Value = Me.txtDisbDate.Value
Worksheets("LoanRates").Cells(i, 14).Value = Me.txtComments.Value Sub
End Sub

 

[Eternallamp]

Did you try the code for Sub cmdSave_Click() that I posted yesterday? That code should have produced a message box like this:
View attachment 107029

which would confirm that the code is running.

Another approach would be to use the VBE debugger to sort this out. Have you tried that yet? You can use the debugger to set a breakpoint in Sub cmdSave_Click() at the beginning of the macro
( i = Me.txtRefNo.Value + 3 would be a good line for the breakpoint). Then run the macro and when it stops at the breakpoint, use the F8 key to single-step through the code as you follow along, using the VBE Watch Window and the VBE Immediate Window to inspect values and cell addresses as necessary to see if the form data is being save or why it is not.

Yes, and thank you. You've given me two great ways to test things out. And yes the message box approached did give me the proper row number that will be updated. Problem is, it just doesn't get updated. Perhaps a stupid question but could it be that when the save action occurs, the selected record event is being triggered. I just can't debug this one. Or maybe it's this older install of Excel, not sure.

 

[rlv01]

Problem is, it just doesn't get updated.

It should not be that hard to debug. The 'update' (Save) mechanism is simple assigment statement:

 .Cells(i, 8).Value = Me.txtIntRate.Value

If say, i = 255 it means the statement writes the value of the user form text box txtIntRate to the cell in row 255, column 8 (cell H255). For that to fail, means that either excel is broken and you need to re-install, or else it is not failing and something else is going on. Though it can happen that a corrupt Excel install could cause strange things to occur, of those two possibilities, it is FAR more likely that something else is going on.

One thing could be that there is nothing in txtIntRate to write, or you are misinterpreting what is happening - one example of this would be if you are expecting the data to be saved to cell H20 when it is actually being saved to cell H255. Or it could be other event code executing and clearing the data, or it could be something you or I have not thought of - which is the point of testing with the debugger as it allows you to slow things down and control the exection to learn about the problem.

Perhaps a stupid question but could it be that when the save action occurs, the selected record event is being triggered

It is possible that other worksheet event code, if used - like code in worksheet event subs Worksheet_Change or Worksheet_SelectionChange could be interfering, but that's where the debugger can help since setting breakpoints and/or single-stepping though the code will soon reveal if that is happening.

 

[DanteAmor]

Another try, replace all your code with the following. Even if you have the listbox's rowsorce property in use, you'll need to clear it.

Option Explicit             'at the beginning of all the code
Dim saving As Boolean

Private Sub lstLoanRates_Click()
  Dim i As Long
  
  If saving = True Then Exit Sub
  i = lstLoanRates.ListIndex + 8
  With Sheets("LoanRates")
    txtRefNo.Value = .Cells(i, 1).Value
    txtLoanNo.Value = .Cells(i, 4).Value
    txtProgId.Value = .Cells(i, 6).Value
    txtIntRate.Value = .Cells(i, 8).Value
    txtDisbDate.Value = .Cells(i, 9).Value
  End With
End Sub

Private Sub cmdSave_Click()
  Dim i As Long
  
  saving = True
  i = lstLoanRates.ListIndex + 8
  With Sheets("LoanRates")
    .Cells(i, 1).Value = txtRefNo.Value
    .Cells(i, 8).Value = txtIntRate.Value
    .Cells(i, 9).Value = txtDisbDate.Value
    .Cells(i, 14).Value = txtComments.Value
  End With
  saving = False
End Sub

Private Sub UserForm_Initialize()
  Dim lr As Long
  Sheets("LoanRates").Select
  lr = Range("A" & Rows.Count).End(3).Row
  
  With lstLoanRates
    .RowSource = "A8:N" & lr
    .ColumnHeads = True
  End With
End Sub

 

[Eternallamp]

You still don't answer my initial question.
You are not putting which instruction and where you have the instruction to load the data into the listbox.

Although I would like to continue helping, it is not possible since you do not provide complete information. Good luck.

My bad. Sorry, I guess I did not understand your question or what you were looking for. Be Well.

 

[dmt32]

However, the update is not being written anywhere in the file. It's a really small file with less than 30 records for testing.

Hi,
when using the Rowsource property of the control you should be aware that you are connecting it directly to your worksheet.
In order to perform certain actions in the worksheet from your userform, you sometimes need to disconnect the control make the changes & then reconnect it.

Not tested but see if this update to your code overcomes your issue

Private Sub cmdSave_Click()
    Dim index       As Long, lr As Long
    Dim r           As Long
    
    Dim wsLoanRates As Worksheet
    
    Set wsLoanRates = ThisWorkbook.Worksheets("LoanRates")
    
    'disconnect rowsource
    With Me.lstLoanRates
        index = .ListIndex
        .RowSource = ""
    End With
    
    r = index + 8
    
    'perform record update
    With wsLoanRates
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        
        .Cells(r, 1).Value = Me.txtRefNo.Value
        .Cells(r, 8).Value = Me.txtIntRate.Value
        .Cells(r, 9).Value = Me.txtDisbDate.Value
        .Cells(r, 14).Value = Me.txtComments.Value
        
    End With
    
    'reconnect rowsource
    With Me.lstLoanRates
        .RowSource = "'" & wsLoanRates.Name & "'!" & "A8:N" & lr
        .ListIndex = index
    End With
    
    'inform user
    MsgBox "Record Updated", 64, "Success"
End Sub

If this still does not resolve for you then suggest that you place a copy of your workbook (with dummy data) in a file sharing site like dropbox & provide a link to it here.
As already seen, there are members here able to assist you.

Dave

 

[DanteAmor]

My bad. Sorry, I guess I did not understand your question or what you were looking for. Be Well.

Did you try the code from post #13?
Somewhere in your code or in the listbox properties you are using the RowSource property (as I mentioned in post #3), but it doesn't matter, just try the code in post #13.