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

 

[NoSparks]

you can select data from the listbox and update using textboxes.

How are you populating the listbox ?

 

[dmt32]

Hi,
your variable needs to be assigned with the correct row value

Assuming your listbox is not filtered then maybe this

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

currentrow = Me.ListBox1.ListIndex + 2
Cells(currentrow, 2) = txtdepot.Text
Cells(currentrow, 3) = txtref_clients.Text



End If
End Sub

This assumes that row one is a header row - change +2 value if this is different as required

Dave

 

[RAires]

@NoSparks I gave the table a name "records" and in the rowsource i used the "records" name.

@dmt32 that works perfectly, but i do want to make a search/filter to look up rows and then update it if needed, would this code not work in that case?

 

[dmt32]

@dmt32 that works perfectly, but i do want to make a search/filter to look up rows and then update it if needed, would this code not work in that case?

If you filter the listbox then the use of listindex will not work correctly.
However, one way to resolve is to return the row number for each record to a hidden column in your listbox & use this value to return it to correct row in your worksheet.

Dave

 

[RAires]

@dmt32 im sorry i'm quite new to this, but how would that work?

 

[dmt32]

@dmt32 im sorry i'm quite new to this, but how would that work?

when you create the code to filter records to your listbox using say a Do or For next loop to step through the records, you add an extra column to the columncount (say you have 10 columns of data you make columncount 11) that is hidden & you use this as a place holder to store the records row number.

currentrow variable is assigned with the value from the last column value of the selected record

something like following

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


    With Me.ListBox1
'get the records row no
        currentrow = Val(.Column(.ColumnCount, .ListIndex))
    End With
    
Cells(currentrow, 2) = txtdepot.Text
Cells(currentrow, 3) = txtref_clients.Text



End If
End Sub

Once you have your filter code - post it if not sure how to include this suggestion.

Dave

 

[RAires]

Thanks Dave, i'm still trying to figure out how to filter the listbox when you type in the search textbox, right now i only managed to make it highlight the row but only if what i type matches something in the first column. This is the hardest part of what i want to do i think. The idea is to make all the other data that doesnt match any of the columns disappear from the listbox, and be able to search based on any value from all the columns that are a total of 98.
Any tips?
This is what i have:

Private Sub txtsearch_Change()
  Dim sFind As String, i
  
    sFind = Me.txtsearch.Text
  
    If Len(sFind) = 0 Then
        Me.lstDisplay.ListIndex = -1
        Me.lstDisplay.TopIndex = 0
    Else
        For i = 0 To Me.lstDisplay.ListCount - 1
            If InStr(UCase(lstDisplay.List(i)), UCase(sFind)) > 0 Then
                Me.lstDisplay.TopIndex = i
                Me.lstDisplay.ListIndex = i
                Exit For
            End If
        Next i
    End If
End Sub

 

[dmt32]

98 columns - that's a lot

are you able to place copy of your workbook with some dummy data in a dropbox & provide a link to it here?

Dave

 

[RAires]

Hi dave, here is the file.
Keep in mind that i have arround 2000rows but only a couple are on the file and i edited the data. In the form at the start i skip some columns on my textboxes but that is on purpose because i dont the user to be able to write on them, just to see them in the listbox
Some columns are empty but that is supposed to be like that thats why i want the search and edit button because more data is supposed to be added later.

I'm not sure if this link will work, but let me know.

Dropbox - File Deleted - Simplify your life

www.dropbox.com