Solved

update a excel database table

Posted on 2004-04-14
1
256 Views
Last Modified: 2010-04-17
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.
0
Comment
Question by:chad201008
1 Comment
 
LVL 3

Accepted Solution

by:
elantra earned 250 total points
ID: 10826231
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()
    'Variables
    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
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
A short article about a problem I had getting the GPS LocationListener working.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now