Populate Table from list box

[riw458]

Hello,
First off this is my first post, normally i can find my solutions but i can't quite figure it out this time, so sorry if this has already been asked.

I'm designing a workbook where users can update the data through a userform.

Currently the userform has a listbox that pulls from a second sheet which is filtered, and displays it in the listbox. This is where the user selects the one they want to update, updates the remaining fields.

Private Sub CommandButton3_Click()
Dim RowNum As Long
Dim SearchRow As Long

RowNum = 2
SearchRow = 2

Worksheets("NewData").Activate


Do Until Cells(RowNum, 1).Value = ""
    If (InStr(1, Cells(RowNum, 2).Value, ComboBox5.Value, vbTextCompare) > 0) And _
            (Cells(RowNum, 3).Value = "void") Then

        Worksheets("Search").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
        Worksheets("Search").Cells(SearchRow, 2).Value = Cells(RowNum, 2).Value
        Worksheets("Search").Cells(SearchRow, 3).Value = Cells(RowNum, 3).Value
        Worksheets("Search").Cells(SearchRow, 4).Value = Cells(RowNum, 4).Value
        Worksheets("Search").Cells(SearchRow, 5).Value = Cells(RowNum, 5).Value
        Worksheets("Search").Cells(SearchRow, 6).Value = Cells(RowNum, 6).Value
        Worksheets("Search").Cells(SearchRow, 7).Value = Cells(RowNum, 7).Value
        Worksheets("Search").Cells(SearchRow, 8).Value = Cells(RowNum, 8).Value
        Worksheets("Search").Cells(SearchRow, 9).Value = Cells(RowNum, 9).Value
        Worksheets("Search").Cells(SearchRow, 10).Value = Cells(RowNum, 10).Value
        Worksheets("Search").Cells(SearchRow, 11).Value = Cells(RowNum, 11).Value
        Worksheets("Search").Cells(SearchRow, 12).Value = Cells(RowNum, 12).Value
        Worksheets("Search").Cells(SearchRow, 13).Value = Cells(RowNum, 13).Value
        Worksheets("Search").Cells(SearchRow, 14).Value = Cells(RowNum, 14).Value
        SearchRow = SearchRow + 1

    End If
    RowNum = RowNum + 1

Loop

If SearchRow = 2 Then
    MsgBox "No Properties Available"
    Exit Sub
End If


ListBox1.RowSource = "SearchResults"


End Sub

The problem i can't get my head around is how to use (dim?) the first cell from the selected item in the listbox, to paste (vlookup basicly) back to main list.

Private Sub CommandButton1_Click()

Call Add_Dynamic_Listbox


         
ActiveCell.Value = ComboBox5.Value
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = "occupied"
ActiveCell.Offset(0, 1).Select

ActiveCell.Value = ComboBox4.Value
ActiveCell.Offset(0, 1).Select

'Col4 - Single Beds Num
'ActiveCell.Value =
'ActiveCell.Offset(0, 1).Select

'Col5 - Double Beds Num
'ActiveCell.Value =
'ActiveCell.Offset(0, 1).Select

'Col6 - Housing Officer
'ActiveCell.Value =
'ActiveCell.Offset(0, 1).Select

ActiveCell.Value = TextBox10.Text
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = TextBox11.Text
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = DateValue(TextBox12.Text)
Selection.NumberFormat = "DD/MM/YYYY"
ActiveCell.Offset(0, 1).Select

'Col9 and 10 - Second tenant
'ActiveCell.Value = TextBox10.Text
'ActiveCell.Offset(0, 1).Select
'ActiveCell.Value = TextBox11.Text
'ActiveCell.Offset(0, 1).Select
ActiveCell.Value = DateValue(TextBox13.Text)
Selection.NumberFormat = "DD/MM/YYYY"
ActiveCell.Offset(0, 1).Select


ActiveCell.Value = TextBox14.Text
ActiveCell.Offset(0, 1).Select

'Col14 - Entry Date
'ActiveCell.Value = DateValue(TextBox13.Text)
'Selection.NumberFormat = "DD/MM/YYYY"
'ActiveCell.Offset(0, 1).Select

'Col16 - Free Text
'ActiveCell.Value = TextBox11.Text
'ActiveCell.Offset(0, 1).Select

Unload Me

End Sub

Any help on this would be appreciated

Thank you!

 

[DanteAmor]

You must store the row number in column O (15) of the "" sheet.

Private Sub CommandButton3_Click()
    Dim RowNum As Long
    Dim SearchRow As Long
    
    RowNum = 2
    SearchRow = 2
    Worksheets("NewData").Activate
    Do Until Cells(RowNum, 1).Value = ""
        If (InStr(1, Cells(RowNum, 2).Value, ComboBox5.Value, vbTextCompare) > 0) And _
                (Cells(RowNum, 3).Value = "void") Then
    
            Worksheets("Search").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
            Worksheets("Search").Cells(SearchRow, 2).Value = Cells(RowNum, 2).Value
            Worksheets("Search").Cells(SearchRow, 3).Value = Cells(RowNum, 3).Value
            Worksheets("Search").Cells(SearchRow, 4).Value = Cells(RowNum, 4).Value
            Worksheets("Search").Cells(SearchRow, 5).Value = Cells(RowNum, 5).Value
            Worksheets("Search").Cells(SearchRow, 6).Value = Cells(RowNum, 6).Value
            Worksheets("Search").Cells(SearchRow, 7).Value = Cells(RowNum, 7).Value
            Worksheets("Search").Cells(SearchRow, 8).Value = Cells(RowNum, 8).Value
            Worksheets("Search").Cells(SearchRow, 9).Value = Cells(RowNum, 9).Value
            Worksheets("Search").Cells(SearchRow, 10).Value = Cells(RowNum, 10).Value
            Worksheets("Search").Cells(SearchRow, 11).Value = Cells(RowNum, 11).Value
            Worksheets("Search").Cells(SearchRow, 12).Value = Cells(RowNum, 12).Value
            Worksheets("Search").Cells(SearchRow, 13).Value = Cells(RowNum, 13).Value
            Worksheets("Search").Cells(SearchRow, 14).Value = Cells(RowNum, 14).Value
            
[COLOR=#ff0000]            Worksheets("Search").Cells(SearchRow, 15).Value = RowNum[/COLOR]
            
            SearchRow = SearchRow + 1
    
        End If
        RowNum = RowNum + 1
    Loop
    If SearchRow = 2 Then
        MsgBox "No Properties Available"
        Exit Sub
    End If
[COLOR=#ff0000]    Dim rng As String[/COLOR]
[COLOR=#ff0000]    rng = Worksheets("Search").Range("A2:O" & Worksheets("Search").Range("A" & Rows.Count).End(xlUp).Row).Address[/COLOR]
[COLOR=#ff0000]    ListBox1.RowSource = Worksheets("Search").Name & "!" & rng[/COLOR]
End Sub

When you are going to carry out the modification, you take the row number from column 14 of the listbox

Private Sub CommandButton1_Click()
    Dim RowNum As Long
    If ListBox1.ListIndex = -1 Then
        MsgBox "Select record"
        Exit Sub
    End If
[B][COLOR=#ff0000]    RowNum = ListBox1.List(ListBox1.ListIndex, 14)[/COLOR][/B]
    
    Cells(RowNum, "A").Value = ComboBox5.Value
    Cells(RowNum, "B").Value = "occupied"
    Cells(RowNum, "C").Value = ComboBox4.Value
    
    Cells(RowNum, "D").Value = TextBox10.Value
    
    '...
    'Continue with all controls
    '...
    
End Sub

 

[riw458]

Thank you for the response Dante!

The first column is the unique ref number that I need to match from the selected line in the listbox back to the NewData sheet of that particular row.

So I changed the 14 to 0, but how to i get it to find the particular line?

TIA

 

[DanteAmor]

Thank you for the response Dante!

The first column is the unique ref number that I need to match from the selected line in the listbox back to the NewData sheet of that particular row.

So I changed the 14 to 0, but how to i get it to find the particular line?

TIA

Try this

Private Sub CommandButton1_Click()
    Dim RowNum As Long
    If ListBox1.ListIndex = -1 Then
        MsgBox "Select record"
        Exit Sub
    End If

[COLOR=#ff0000]dim UniqueRefNumber as string, b as range

[/COLOR]
[COLOR=#ff0000]    UniqueRefNumber= ListBox1.List(ListBox1.ListIndex, 0)

set b = range("A:A").Find(UniqueRefNumber, lookat:=xlwhole)
if not b is nothing then
   RowNum = b.row
end if[/COLOR]
    
    Cells(RowNum, "A").Value = ComboBox5.Value
    Cells(RowNum, "B").Value = "occupied"
    Cells(RowNum, "C").Value = ComboBox4.Value
    
    Cells(RowNum, "D").Value = TextBox10.Value
    
    '...
    'Continue with all controls
    '...
    
End Sub

 

[riw458]

Thank you so much! Really helpful, all sorted on that bit now

 

[DanteAmor]

Thank you so much! Really helpful, all sorted on that bit now

I'm glad to help you. Thanks for the feedback.