?
Solved

update a excel database table

Posted on 2004-04-14
1
Medium Priority
?
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 3

Accepted Solution

by:
elantra earned 1000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
A short article about problems I had with the new location API and permissions in Marshmallow
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Six Sigma Control Plans

777 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