How to fix this bug in my code that doesn't al

Question

Hello I've been working on this excel userform for a month now and I'm almost done. But there's a bug in my code that doesn't show any errors but it won't let me update the rest of the columns.

When I click on the "View List" button, the data in the spreadsheet will be displayed in the ListBox.

And if I click on a value, say I click "dfldasfjasldk", its row values will be displayed on the ComboBoxes, allowing the user to edit the information.

Image :

And when I click on the "Update Row" the only updated column is the environment. It didn't work for the rest of the columns.

     Private Sub btnDelete_Click()

        Dim a As Integer

            If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Yes") = vbYes Then

                For a = 1 To Range("A100000").End(xlUp).Row
                    If Cells(a, 1) = listHeader.List(listHeader.ListIndex) Then
                    Rows(a).Select
                    Selection.Delete
                End If
            Next a
        End If

    End Sub


    Private Sub btnView_Click()

        listHeader.RowSource = "A4:H200"

    End Sub

Private Sub cmbAdd_Click()
    Dim sheet As Worksheet
    Set sheet = ThisWorkbook.Sheets("PRESTAGE DB")

    nextrow = sheet.Cells(Rows.Count, 1).End(xlUp).Row + 1

    sheet.Cells(nextrow, 1) = Me.cmbSchema
    sheet.Cells(nextrow, 2) = Me.cmbEnvironment
    sheet.Cells(nextrow, 3) = Me.cmbHost
    sheet.Cells(nextrow, 4) = Me.cmbIP
    sheet.Cells(nextrow, 5) = Me.cmbAccessible
    sheet.Cells(nextrow, 6) = Me.cmbLast
    sheet.Cells(nextrow, 7) = Me.cmbConfirmation
    sheet.Cells(nextrow, 8) = Me.cmbProjects

    MsgBox "Data Added!"

End Sub

Private Sub cmbClearFields_Click()

    cmbSchema.Text = ""
    cmbEnvironment.Text = ""
    cmbHost.Text = ""
    cmbIP.Text = ""
    cmbAccessible.Text = ""
    cmbLast.Text = ""
    cmbConfirmation.Text = ""
    cmbProjects.Text = ""
    cmbSearch.Text = ""

End Sub

 Private Sub cmbSearch_Change()

    x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
        For y = 2 To x
            If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSearch.Value Then
                cmbSchema.Text = Sheets("PRESTAGE DB").Cells(y, 1)
                cmbEnvironment.Text = Sheets("PRESTAGE DB").Cells(y, 2)
                cmbHost.Text = Sheets("PRESTAGE DB").Cells(y, 3)
                cmbIP.Text = Sheets("PRESTAGE DB").Cells(y, 4)
                cmbAccessible.Text = Sheets("PRESTAGE DB").Cells(y, 5)
                cmbLast.Text = Sheets("PRESTAGE DB").Cells(y, 6)
                cmbConfirmation.Text = Sheets("PRESTAGE DB").Cells(y, 7)
                cmbProjects.Text = Sheets("PRESTAGE DB").Cells(y, 8)

                UserForm1.listHeader.RowSource = "A" + CStr(y) + ": H" + CStr(y)
                Exit For
            End If
        Next y

'Dim x As Long
'Dim y As Long

'x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
'For y = 2 To x

'If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSearch.Value Then
    'cmbSchema.Text = Sheets("PRESTAGE DB").Cells(y, 1)
    'cmbEnvironment.Text = Sheets("PRESTAGE DB").Cells(y, 2)
    'cmbHost.Text = Sheets("PRESTAGE DB").Cells(y, 3)
    'cmbIP.Text = Sheets("PRESTAGE DB").Cells(y, 4)
    'cmbAccessible.Text = Sheets("PRESTAGE DB").Cells(y, 5)
    'cmbLast.Text = Sheets("PRESTAGE DB").Cells(y, 6)
    'cmbConfirmation.Text = Sheets("PRESTAGE DB").Cells(y, 7)
    'cmbProjects.Text = Sheets("PRESTAGE DB").Cells(y, 8)

'End If
'Next y

End Sub

     Private Sub cmbUpdate_Click()

     Dim x As Long
     Dim y As Long

        x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
        For y = 2 To x
            If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSchema.Value Then
                Sheets("PRESTAGE DB").Cells(y, 2) = cmbEnvironment.Value
                Sheets("PRESTAGE DB").Cells(y, 3) = cmbHost.Value
                Sheets("PRESTAGE DB").Cells(y, 4) = cmbIP.Value
                Sheets("PRESTAGE DB").Cells(y, 5) = cmbAccessible.Value
                Sheets("PRESTAGE DB").Cells(y, 6) = cmbLast.Value
                Sheets("PRESTAGE DB").Cells(y, 7) = cmbConfirmation.Value
                Sheets("PRESTAGE DB").Cells(y, 8) = cmbProjects.Value
        Exit For
            End If
    Next y

    'Dim x As Long
    'Dim y As Long

    'x = Sheets("PRESTAGE DB").Range("A" & Rows.Count).End(xlUp).Row
    'For y = 2 To x
    'If Sheets("PRESTAGE DB").Cells(y, 1).Text = cmbSchema.Value Then
    'Sheets("PRESTAGE DB").Cells(y, 2) = cmbEnvironment
    'Sheets("PRESTAGE DB").Cells(y, 3) = cmbHost
    'Sheets("PRESTAGE DB").Cells(y, 4) = cmbIP
    'Sheets("PRESTAGE DB").Cells(y, 5) = cmbAccessible
    'Sheets("PRESTAGE DB").Cells(y, 6) = cmbLast
    'Sheets("PRESTAGE DB").Cells(y, 7) = cmbConfirmation
    'Sheets("PRESTAGE DB").Cells(y, 8) = cmbProjects

    'End If
    'Next y

    End Sub

    Private Sub CommandButton5_Click()
        listHeader.RowSource = ""

    End Sub


    Private Sub listHeader_Click()

        cmbSchema.Value = UserForm1.listHeader.Column(0)
        cmbEnvironment.Value = UserForm1.listHeader.Column(1)
        cmbHost.Value = UserForm1.listHeader.Column(2)
        cmbIP.Value = UserForm1.listHeader.Column(3)
        cmbAccessible.Value = UserForm1.listHeader.Column(4)
        cmbLast.Value = UserForm1.listHeader.Column(5)
        cmbConfirmation.Value = UserForm1.listHeader.Column(6)
        cmbProjects.Value = UserForm1.listHeader.Column(7)

    End Sub


    Private Sub UserForm_Initialize()

        cmbSearch.List = Sheets("PRESTAGE DB").Range("A4:A10000").Value

    End Sub

what do you think is causing the problem? Link to the file: https://jmp.sh/8cDUORV

Answer 1

When you use the RowSource property to load data into a ListBox, some problems and drawbacks may arise. If I were you, I'd try to avoid it.

With that in mind, please take a look of this demo where I show how to do the basic operations (Create, Read, Update, Delete) with Excel Data loaded into a Listbox inside a Userform.

Maybe you can adapt it to fulfill your needs. It's not the ideal solution (for example the lack of error handling and some code that is repeated) but should point you in the right direction.

Visit this github repository and download the xlsm file:

https://github.com/rdiazjimenez/excel-vba-userform-basic-listbox-demo

Also read the readme.md file to see some instructions.

---

Here are some tips to have in mind when you design a solution that includes loading Excel data into a Listbox in a UserForm:

1) Try to store the Excel information into an structured Excel Table (Visit this link to learn more: https://support.office.com/en-ie/article/create-and-format-tables-e81aa349-b006-4f8a-9806-5af9df0ac664)

2) Use Option Explicit at the top of each module in VBA so you have more control of the variables you use and create (Visit this link to learn more: https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-explicit-statement)

3) Use descriptive names for controls inside a UserForm (Visit this link to learn more: https://rtmccormick.com/2015/11/23/vba-control-naming-conventions/)

---

Please mark this answer if this helped you.