update a excel database table

i have one sheet with 5 cells that i want to go into a new line of a excel database on another sheet when i click on a update button.  one of the five cells is an order number and i would also like a button that when clicked asked for the order number, then populated the other cells with the info from the database.
Who is Participating?
elantraConnect With a Mentor Commented:
Here you go, the following code is for a button named "cmdSelectOrder".  You have to put the button on the sheet with all your order numbers.  I'm sure you could easily modify the code if you want it to do other things.

Private Sub cmdSelectOrder_Click()
    Dim strInput As String
    Dim rngOrders As Range
    Dim bolFound As Boolean
    Dim objDestination As Worksheet
    'Change to accomodate your destination sheet name
    Set objDestination = ActiveWorkbook.Sheets("Sheet2")
    'Prompt for input
    strInput = InputBox("Please enter an order number:", "Order Number")
    'Set the used range
    Set rngOrders = ActiveSheet.UsedRange
    'Count used range in sheet and loop through each line
    For i% = 1 To rngOrders.Rows.Count
        Cells(i%, 1).Select
        If ActiveCell.Value = strInput Then
            bolFound = True
            With ActiveCell.EntireRow
                .Copy objDestination.Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
            End With
        End If
    Next i%
    'If the order was not found then display an error message
    If bolFound = False Then MsgBox "Invalid order number selected!"
End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.