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

 

[DanteAmor]

Hi and welcome to MrExcel

What instruction or code do you use to load the data into the listbox?

Can you put that code or instruction here?

 

[DanteAmor]

The problem is that you are loading the data into the listbox with the RowSource property.
Delete what you have to load the data.

Put the following code in your form:

Private Sub UserForm_Initialize()
  Dim lr As Long
  lr = Sheets("LoanRates").Range("A" & Rows.Count).End(3).Row
  lstLoanRates.List = Sheets("LoanRates").Range("A4:N" & lr).Value
End Sub

I made some abbreviations in your code, change it to the following:

Private Sub lstLoanRates_Click()
  Dim i As Long
  
  i = lstLoanRates.ListIndex + 4
  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
  
  i = txtRefNo.Value + 3
  With Sheets("LoanRates")
    .Cells(i, 1).Value = txtRefNo.Value 'Then
    .Cells(i, 8).Value = Me.txtIntRate.Value
    .Cells(i, 9).Value = Me.txtDisbDate.Value
    .Cells(i, 14).Value = Me.txtComments.Value
  End With
End Sub

 

[rlv01]

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.

In looking at your code I wonder if the problem is that your save command is putting the data where you don't see it. The row where the data gets saved is determined by the value in text box txtRefNo.

i = Me.txtRefNo.Value + 3

If the value in txtRefNo was very large, say 950, the value would be saved to row 953, which could be well off the visible part of the screen. Here is some code to test that theory

Private Sub cmdSave_Click()
    Dim i As Long
    
    i = Me.txtRefNo.Value + 3
    With Worksheets("LoanRates")
        .Activate
        If MsgBox("User Form text box data will be saved to worksheet row " & i & _
        vbCrLf & vbCrLf & "Proceed?", vbYesNo Or vbQuestion, Application.Name) = vbYes Then
            .Cells(i, 1).Select
            .Cells(i, 1).Value = Me.txtRefNo.Value
            .Cells(i, 8).Value = Me.txtIntRate.Value
            .Cells(i, 9).Value = Me.txtDisbDate.Value
            .Cells(i, 14).Value = Me.txtComments.Value
        End If
    End With
End Sub

 

[Eternallamp]

The problem is that you are loading the data into the listbox with the RowSource property.
Delete what you have to load the data.

Put the following code in your form:

Private Sub UserForm_Initialize()
  Dim lr As Long
  lr = Sheets("LoanRates").Range("A" & Rows.Count).End(3).Row
  lstLoanRates.List = Sheets("LoanRates").Range("A4:N" & lr).Value
End Sub

I made some abbreviations in your code, change it to the following:

Private Sub lstLoanRates_Click()
  Dim i As Long
 
  i = lstLoanRates.ListIndex + 4
  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
 
  i = txtRefNo.Value + 3
  With Sheets("LoanRates")
    .Cells(i, 1).Value = txtRefNo.Value 'Then
    .Cells(i, 8).Value = Me.txtIntRate.Value
    .Cells(i, 9).Value = Me.txtDisbDate.Value
    .Cells(i, 14).Value = Me.txtComments.Value
  End With
End Sub

Thank you for your approach. Your code is well structured and clean. I did try to move away from using the Row Source property and it resulted in losing the column headers / labels. Save does work as long as I don't click on the list to retrieve a record. For now due to time constraints, I will move away from attempting to update and save a row returned from clicking in the list box.

 

[Eternallamp]

In looking at your code I wonder if the problem is that your save command is putting the data where you don't see it. The row where the data gets saved is determined by the value in text box txtRefNo.

i = Me.txtRefNo.Value + 3

If the value in txtRefNo was very large, say 950, the value would be saved to row 953, which could be well off the visible part of the screen. Here is some code to test that theory

Private Sub cmdSave_Click()
    Dim i As Long
   
    i = Me.txtRefNo.Value + 3
    With Worksheets("LoanRates")
        .Activate
        If MsgBox("User Form text box data will be saved to worksheet row " & i & _
        vbCrLf & vbCrLf & "Proceed?", vbYesNo Or vbQuestion, Application.Name) = vbYes Then
            .Cells(i, 1).Select
            .Cells(i, 1).Value = Me.txtRefNo.Value
            .Cells(i, 8).Value = Me.txtIntRate.Value
            .Cells(i, 9).Value = Me.txtDisbDate.Value
            .Cells(i, 14).Value = Me.txtComments.Value
        End If
    End With
End Sub

Thank you for your response. I did try to see where the record is being written. It does appear the save option is functioning, i.e., the screen flickers. However, the update is not being written anywhere in the file. It's a really small file with less than 30 records for testing.

 

[rlv01]

It does appear the save option is functioning, i.e., the screen flickers. However, the update is not being written anywhere in the file. It's a really small file with less than 30 records for testing.

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

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.

 

[DanteAmor]

I did try to move away from using the Row Source property and it resulted in losing the column headers / labels

You didn't answer my question from post #2

What instruction or code do you use to load the data into the listbox?

Can you put that code or instruction here?

If you don't put everything related to your code here, we'll just be guessing.

 

[Eternallamp]

You didn't answer my question from post #2




If you don't put everything related to your code here, we'll just be guessing.

Yes, Thank you. So when I initially invoke the form, the following code is used to load the records. Note, I repositioned the data to start at row 8 in the table.
Sub Button11_Click()
Dim i As Integer
Worksheets("LoanRates").Select
Load frmLoanRates
i = 8
frmLoanRates.txtRefNo.Value = Worksheets("LoanRates").Cells(i, 1).Value
frmLoanRates.txtIntRate.Value = Worksheets("LoanRates").Cells(i, 8).Value
frmLoanRates.txtIntRate.SetFocus
frmLoanRates.txtDisbDate.Value = Worksheets("LoanRates").Cells(i, 9).Value
frmLoanRates.txtComments.Value = Worksheets("LoanRates").Cells(i, 14).Value

' This command will display the rates form input screen used in the LoanRates Tab
frmLoanRates.Show
End Sub

You wrote to discontinue the use of row source. In doing so, I lost the column headers. I know there must be an alternative solution to keep the column headers intact. the novice currently in me could not track it down.

 

[DanteAmor]

What instruction or code do you use to load the data into the listbox?

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.