How to populate Combobox with the values listed in COLUMN A (with no manual coding)?

Question

Hello everyone! I've solved my first question about the update button. It's quite working now. I figured that it is IMPOSSIBLE to Update a row WITHOUT a key or a unique ID (so I made the column A as unique id).

1. I added another combobox located at the top of the listbox for searching/filtering. However I don't know how to populate the combobox WITHOUT MANUALLY CODING it like this one: Stackoverflow . The reason why I don't want it to be that way is because the user would be adding a row using a userform from time to time and the rows would be thousands.

Is there any way I can populate the combobox without manually typing every value?

---

Just an update about my issue yesterday, here is the updated code and updated UI:

     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 btnSearch_Click()

'IM THINKING ABOUT REMOVING THE SEARCH BUTTON BECAUSE THE COMBOBOX ITSELF CAN BE USED FOR SEARCHING THE ROW
'IT MAKES THE SEARCH BUTTON USELESS

'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 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

End Sub

Private Sub cmbSearch_Change()

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
    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()

    'Dim rngMyData As Range
    Dim x As Long
    Dim y As Long

        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

Some problems of the moment:

1. The Search Button works by displaying the row values in the comboboxes below BUT NOT in the listbox.

2. The UPDATE BUTTON ONLY WORKS through the SEARCH BUTTON. As mentioned, the search button displays the row value in the combobox, then the user will type/edit value in the combobox, click the UPDATE BUTTON and the row is updated like it should be.

3. The UPDATE BUTTON DOES NOT WORK when I click on the "View List" button and I select a row from the listbox. The row value is still displayed in the comboboxes but when I try to edit a value and click the update button, it doest work any more.

It's confusing but i'm really trying to figure everything out.

Answer 1

from the hints you're giving, the "most probable" solution is to substitute all those Me.Cells with ThisWorkbook.Sheets("TRY DB").Cells

so you may want to use a With myObject...End With block and begin with a dot (.) all object references that you want to refer to myObject as follows:

Private Sub cmbUpdate_Click()

    Dim z As Long
    Dim x As Long

    With ThisWorkbook.Sheets("TRY DB") ' reference wanted sheet in wanted workbook

        z = Application.WorksheetFunction.CountA(.Range("A:A")) 

        For x = 2 To z
            If .Cells(x, "A").Value = Me.cmbSchema.Text Then
                .Cells(x, "B").Value = Me.cmbEnvironment.Text
                .Cells(x, "C").Value = Me.cmbHost.Text
                .Cells(x, "D").Value = Me.cmbIP.Text
                .Cells(x, "E").Value = Me.cmbAccessible.Text
                .Cells(x, "F").Value = Me.cmbLast.Text
                .Cells(x, "G").Value = Me.cmbConfirmation.Text
                .Cells(x, "H").Value = Me.cmbProjects.Text

                ' exit for ' <-- uncomment this line if your data layout is such that you are to update only one line
            End If
        Next

    End With
End Sub

BTW in standing listHeader.RowSource = "A4:H1000" in your Sub btnView_Click(), you may also want to change For x = 2 To z to For x = 4 To z