Select listbox items and edit in VBA

[terriertrip]

I have a problem with my listbox edit sub. Userform contains 3 textboxes to add/update/delete items (3 columns) to a list box. The items in the list are stored in a sheet. I want the user to select any item from the list and update the items from the textboxes. The textboxes populate the listbox selection. The code works to populate except that 1.) when an item is selected, the actual update is applied to the last item in the row and not the user selected item from the listbox.

Option Explicit

Const miROW_NO__HEADER  As Integer = 1
Const miCOL_NO__FM    As Integer = 1
Const miCOL_NO__TVD   As Integer = 2
Const miCOL_NO__MD     As Integer = 3
Const msTEST_COLUMN     As String = "A"
Const msSHEET_NAME      As String = "Sheet1"

Dim miRowNo_Current     As Integer
Dim miRowNo_Last        As Integer

Private Sub UserForm_Initialize()

    Const sTEST_COLUMN  As String = "A"
    Dim Rng    As Range
    Dim lastrow As Long
    Dim LastCol As Long

    With Sheets(msSHEET_NAME)

        lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        Set Rng = .Range(Cells(2, 1), Cells(lastrow, 1))

    ' Populate controls only if the worksheet contains at least one data row
        If .Range(sTEST_COLUMN & miROW_NO__HEADER).Offset(1, 0).Value <> vbNullString Then

        miRowNo_Last = .Range(sTEST_COLUMN & .Rows.Count).End(xlUp).Row
        miRowNo_Current = miRowNo_Last

    Me.txtFm.Text = .Cells(miRowNo_Last, miCOL_NO__FM).Text
    Me.txtMD.Text = .Cells(miRowNo_Last, miCOL_NO__MD).Text
    Me.txtTVD.Text = .Cells(miRowNo_Last, miCOL_NO__TVD).Text
        End If
    End With

    Set Rng = Nothing
      Me.Repaint
'    txtFm.SetFocus
   
    Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range
    Dim cLastRow As Long
    Dim bLastRow As Long
    
    bLastRow = Sheets(msSHEET_NAME).Range("A" & Rows.Count).End(xlUp).Row
    
' populates listbox with data
    With Sheets(msSHEET_NAME)
        Set rngSource = .Range("A2:C" & bLastRow)
        Set lbtarget = Me.ListBox1
        
        With lbtarget
            .ColumnCount = 3
            .ColumnWidths = "185;50;40"
            .List = rngSource.Cells.Value
        End With
    End With
    
  txtFm.SetFocus
  
End Sub

Private Sub ListBox1_click()

    With ThisWorkbook.Sheets(msSHEET_NAME)
    
    txtFm.Text = Me.ListBox1.List(ListBox1.ListIndex, 0)
    txtMD.Text = Me.ListBox1.List(ListBox1.ListIndex, 1)
    txtTVD.Text = Me.ListBox1.List(ListBox1.ListIndex, 2)
    
    End With
End Sub

Private Sub cmdUpdate_Click()

  If MsgBox("You are about to update your formation information.", vbYesNo + vbDefaultButton2, "Update Formation Record") = vbNo Then
    Else
    With ThisWorkbook.Sheets(msSHEET_NAME)

        .Cells(miRowNo_Current, miCOL_NO__FM).Value = Me.txtFm.Text
        .Cells(miRowNo_Current, miCOL_NO__MD).Value = Me.txtMD.Text
        .Cells(miRowNo_Current, miCOL_NO__TVD).Value = Me.txtTVD.Text

    End With
    End If
    
    UserForm_Initialize

End Sub

Best regards.

[terriertrip]

I've simplified the problem. Can anybody help with this, please??

[Arkadi]

Can you provide the workbook? With userforms especially, it is very hard for us to re-create the environment to see what is going on...
Clean out any sensitive data, we just need a few rows of random data, and the userform in question.

[Norie]

You need to set miRowNo_Current to the row the data has come from and you can probably do that by using the ListIndex property of the listbox.

miRowNo_Current = Me.ListBox1.ListIndex + 2

[terriertrip]

Thanks, Norie! That works perfectly.