Vba UpdateRow

[RAires]

Hello everyone, hope you're having a great day.

I made a userform with a listbox that reads from a database in excel. I added some buttons like add, delete and i added a update button so you can select data from the listbox and update using textboxes.

The problem is that when i use the button it only updates the first row and i dont know what i am missing in my code so it updates the row i selected instead of just updating the first row.

any help would be great.



Here is the code i have:
Ps: I have 98 columns of data but i only put the first 2 just to show what i have.

Dim currentrow As Long
Private Sub UserForm_Initialize()

currentrow = 6

txtdepot = Cells(currentrow, 2)
txtref_clients = Cells(currentrow, 3)


Private Sub cmdUpdate_Click()
Dim answer
answer = MsgBox("Confirm if you want  to update the record?", vbYesNo + vbQuestion, "Update Record")
If answer = vbYes Then

Cells(currentrow, 2) = txtdepot.Text
Cells(currentrow, 3) = txtref_clients.Text



End If
End Sub

 

[dmt32]

File downloaded & had a cursory glance - its a complex project but will see what time I can find.
Which column in table do you want the filter to apply to?

Dave

 

[RAires]

File downloaded & had a cursory glance - its a complex project but will see what time I can find.
Which column in table do you want the filter to apply to?

Dave

Ideally all of them, unless it's really a lot of work and not a viable option.

Thanks again for the help!

 

[dmt32]

Ideally all of them, unless it's really a lot of work and not a viable option.

Not sure about an approach for all of them maybe another here could suggest solution for this - also I note that you are using rowsource to populate the listbox presumably, as you want to display the headings?
Rowsource cannot refer to filtered (discontinuous) ranges. The visible cells of filtered range would need to be copied to another range for the rowsource address to reference.

Dave

 

[RAires]

Yeah i'm using rowsource because i want the headings.
So if i want to only filter using only the values of 3 columns but show all the columns when i filter it would be easier right? and would i still need to make another sheet so i can copy the visible cells there and read the rows from there when i filter?

 

[dmt32]

Yeah i'm using rowsource because i want the headings.
So if i want to only filter using only the values of 3 columns but show all the columns when i filter it would be easier right? and would i still need to make another sheet so i can copy the visible cells there and read the rows from there when i filter?

You could add a combobox with the column headings & select from that which column you want to filter against - you would need to copy the filtered data to another sheet & apply the rowsource property of the listbox control to the new range

Dave

 

[NoSparks]

Perhaps something along the lines of this will work, the file at the link you posted has been deleted so I can only test this with what I set up
sheets are referred to by their code names, sheet1 being the database sheet and sheet2 the 'make another sheet'
assume the database sheet has only 1 table

Copy your database header row to that 'make another sheet' starting in A1

Private Sub UserForm_Initialize()
    Dim ray As Variant
    
Sheet2.UsedRange.Offset(1).Delete
ray = Sheet1.ListObjects(1).DataBodyRange.Value
Sheet2.Cells(2, 1).Resize(UBound(ray, 1), UBound(ray, 2)).Value = ray

Me.ListBox1.RowSource = Sheet2.Cells(1).CurrentRegion.Address
Me.TextBox1 = vbNullString

End Sub

Private Sub TextBox1_Change()
    Dim ray1() As Variant, ray2() As Variant
    Dim i As Long, j As Long, k As Long
    Dim ii As Long
    Dim srchStr
    
Application.ScreenUpdating = False

srchStr = Me.TextBox1
'clear out sheet2
Sheet2.UsedRange.Offset(1).Delete
'all data into an array (1 base)
ray1 = Sheet1.ListObjects(1).DataBodyRange.Value
'size second array to max possible size (0 base)
ReDim ray2(0 To UBound(ray1, 1), 0 To UBound(ray1, 2))

    For i = LBound(ray1, 1) To UBound(ray1, 1)      'rows
        For j = LBound(ray1, 2) To UBound(ray1, 2)  'columns
            If ray1(i, j) Like "*" & srchStr & "*" Then
                For k = 1 To UBound(ray1, 2)
                    ray2(ii, k - 1) = ray1(i, k)
                Next k
                ii = ii + 1
                Exit For
            End If
        Next j
    Next i

Sheet2.Cells(2, 1).Resize(UBound(ray1, 1), UBound(ray1, 2)).Value = ray2
Me.ListBox1.RowSource = Sheet2.Cells(1).CurrentRegion.Address

Application.ScreenUpdating = True

End Sub