excel 2003

[Guest]

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

 

[JW]

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-

 

[Guest]

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.