Update Listbox with "Update" button both embedded in spreadsheet

user_19550593 0
Viewed 32

I have a spreadsheet that has a Listbox with multiple columns displaying values from a different sheet and when I double click a row in the listbox, the values are presented in cells G7:M7 of the active sheet I am in. Now I created a code for the update button to update the item in the listbox and on the sheet it is linked to, but it runs into the error of application defined error. Not sure if im using the wrong worksheet function. "G5" is the activator that identifies which item from the list box is displayedenter image description herethis table linked to listbox.

Private Sub CommandButton2_Click()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("lookup")
Dim selected_row As Long
selected_row = Application.WorksheetFunction.Match(Range("G5"), sh.Range("A:A"), 0)
sh.Range("J" & selected_row).Value = Range("G7").Value
sh.Range("K" & selected_row).Value = Range("H7").Value
sh.Range("L" & selected_row).Value = Range("I7").Value
sh.Range("M" & selected_row).Value = Range("J7").Value
sh.Range("N" & selected_row).Value = Range("K7").Value
sh.Range("O" & selected_row).Value = Range("L7").Value
sh.Range("P" & selected_row).Value = Range("M7").Value
End Sub
vba excel
1 Answers

The error thrown when there is no match is a 1004. You could trap for that like this:

Private Sub CommandButton2_Click()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("lookup")
Dim selected_row As Long
    on error resume next
        selected_row = Application.WorksheetFunction.Match(ActiveSheet.Range("G5").value, 
sh.Range("A:A"), 0)
    on error goto 0
    if selected_row <> 0 then
        sh.cells(10 , selected_row).resize(1,7).Value = ActiveSheet.Range("G7").resize(1,7).Value
    else
        call vba.msgbox("Unable to find " & Range("G5").value & " in the lookup table")
    end if
End Sub
user_145256060
Related