Why can't I UPDATE the SELECTED value from the ListBox?

[Stellaaaa]

Hi so I've been doing this macro for 2 months already it's almost done. It's my first time to code so I'm really new.

The only problem left is that I don't know why the UserForm doesn't allow me to Update a selected row from the ListBox? It only Updates the 'Environment' column.
I don't get errors but it's not working.

    
         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
        
    If Me.cmbSchema = "" Or _
        Me.cmbEnvironment = "" Or _
        Me.cmbHost = "" Or _
        Me.cmbIP = "" Or _
        Me.cmbAccessible = "" Or _
        Me.cmbLast = "" Then


        MsgBox "Some fields cannot be blank!", vbCritical, "Data Missing"


    Else
        
        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 If
    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 = ThisWorkbook.Worksheets("PRESTAGE DB") 'JAN 30
        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
    
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
        
        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
            cmbEnvironment.AddItem "DEV"
            cmbEnvironment.AddItem "UAT"
            cmbEnvironment.AddItem "SIT"
            cmbEnvironment.AddItem "QA"
            cmbEnvironment.AddItem "PROD"
            
            cmbAccessible.AddItem "Y"
            cmbAccessible.AddItem "N"
            
            cmbIP.AddItem "1521"
            
            cmbProjects.AddItem "DP - proposed for DEV/SIT"
            cmbProjects.AddItem "PH EFUSE SIT"
            cmbProjects.AddItem "MyAXA SG DEV/DIT"
            
            
        
        End Sub

[Jan Karel Pieterse]

Not sure what the problem is, for me it worked just fine (updated the second column value for the first row of data).

[Stellaaaa]

it only works for the second column. Not for the rest.

[Jan Karel Pieterse]

PLease describe your exatc steps, it works for me:

[Stellaaaa]

like all the columns? Okay here's the step by step

1. Click on View List > Data will show in ListBox
2. Select one row (any row) and it will appear on the comboboxes below.
3. Try to edit ALL the values from the Combobox
4. Click "UPDATE"
5. It will go back to it's original value. No changes will happen.

That's what is happening

[Jan Karel Pieterse]

Not for me, I took precisely those steps and each column was updated accordingly!

[Stellaaaa]

I don't know why it doesn't work for me? It only changes the Environment column. What do you think is the problem with my machine?

[Jan Karel Pieterse]

I really don't know.

[大灰狼1976]

I noticed that too. I wonder why the listHeader_Click() event was triggered when writing to the cell.

[大灰狼1976]

I found the reason and tried to solve it.

[大灰狼1976]

Hi Stellaaaa!
The problem has been solved. Please refer to the attached.

[Jan Karel Pieterse]

Aha, now I understand. This is beacuse you are using the RowSOurce property, which will cause the listbox to fire events if the source cells are changed, which is what your update button does. In general I always advise not to use the RowSOurce property to fill a listbox, but rather to use either ListBox.List = Range("A1:V20").Value or use AddItem to add items one at the time.

[大灰狼1976]

But RowSOurce property has its advantages. It can update LISTBOX in real time without doing anything.

[Jan Karel Pieterse]

Which is precisely the reason not to use it, you loose control. Also there have been reports about instability.