excel 2003
I have a blank invoice layout in excel.
I need to have it ask the user when they either open the file or when they click on cell for customer # user then enters it -select enter excel then goes to my sql db and finds customer # and pulls and populates sold to address area in excel, then prompts user for ship to same as sold -yes or no. if yes fill in ship to if no then user can fill this in. Any ideas on how to do this |
excel 2003
On May 9, 5:44 pm, lyeoman wrote:
I have a blank invoice layout in excel. I need to have it ask the user when they either open the file or when they click on cell for customer # user then enters it -select enter excel then goes to my sql db and finds customer # and pulls and populates sold to address area in excel, then prompts user for ship to same as sold -yes or no. if yes fill in ship to if no then user can fill this in. Any ideas on how to do this Just use the Workbook_Open event to fire the code as soon as it is opened. Store the users customer # entry in a variable and pass that to your Microsoft Query code. After it is returned, you a MsgBox with a vbYesNo button to ask about the shipping. If the user selects vbYes, fill in the shipping info. That's about as much help as I can give without seeing the spreadsheet. HTH -Jeff- |
excel 2003
Assume customer # in Range("A1"), code goes in ThisWorkbook module:
Private Sub Workbooks_Open() Range("A1").Activate If Cells(1, 1) < "" Then Cells(1, 1).ClearContents End If Cells(1, 1) = InputBox("Enter Customer Number for data required.", "CUSTOMER NUMBER") '<Call SQLMacro You will need to provide this ShpAddrs = MsgBox("IS THE SHIP TO ADDRESS THE SAME AS _ THE SOLD TO ADDRESS?", vbYesNo + vbQuestion, "SHIPPING ADDRESS" If ShpAddrs = vbNo Then ShpTo = InputBox(Enter the shipping address for this order.", "SHIP TO") Range(NeedToDesignate) = ShpTo 'Need to determine Range End If End Sub I'm not sure what your SQL db is so you will have to work that out. The rest of the code should give you an idea of how to do it. It is just a matter of using InputBoxes and Message Boxes to get the user to respond. "lyeoman" wrote: I have a blank invoice layout in excel. I need to have it ask the user when they either open the file or when they click on cell for customer # user then enters it -select enter excel then goes to my sql db and finds customer # and pulls and populates sold to address area in excel, then prompts user for ship to same as sold -yes or no. if yes fill in ship to if no then user can fill this in. Any ideas on how to do this |
All times are GMT +1. The time now is 01:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com