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.