I have an order form worksheet that uses a drop down list from a named range in another worksheet, PriceList. Above the order area are some blank cells for entering the customer name, shipping address, etc. The PriceList worksheet has a data connection to an Access database.
My problem is, if the customer info section is filled out first, the drop down list of item numbers is empty. In order to fill out the form, the salesperson is having to enter all the items, save the file, then open it up again to enter the customer info. Can someone tell me why this is happening?
This the code I'm using for the item number lookup, to populate the item description and prices:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim wsLists As Worksheet
Dim ItemNoRow As Long
Dim ItemDescRow As Long
On Error GoTo errHandler
Set wsLists = Worksheets("PriceList")
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Select Case Target.Column
Case 2
With Target
If .Value = "" Then
.Offset(0, 1).Value = ""
Else
ItemNoRow = Application.Match(.Value, wsLists.Range("MASNo"), 0)
.Offset(0, 1).Value = wsLists.Range("ItemDesc")(ItemNoRow).Value
.Offset(0, 4).Value = wsLists.Range("PriceEa")(ItemNoRow).Value
.Offset(0, 5).Value = wsLists.Range("PackQty")(ItemNoRow).Value
.Offset(0, 6).Value = wsLists.Range("PricePk")(ItemNoRow).Value
.Offset(0, 13).Value = wsLists.Range("FazeO")(ItemNoRow).Value
End If
End With
Case 3
With Target
If .Value = "" Then
.Offset(0, -1).Value = ""
Else
ItemDescRow = Application.Match(.Value, wsLists.Range("ItemDesc"), 0)
.Offset(0, -1).Value = wsLists.Range("MASno")(ItemDescRow).Value
.Offset(0, 1).Value = wsLists.Range("PriceEa")(ItemDescRow).Value
.Offset(0, 2).Value = wsLists.Range("PackQty")(ItemDescRow).Value
.Offset(0, 3).Value = wsLists.Range("PricePk")(ItemDescRow).Value
.Offset(0, 10).Value = wsLists.Range("FazeO")(ItemDescRow).Value
End If
End With
Case Else
'do nothing
End Select
exitHandler:
Application.EnableEvents = True
Exit Sub
errHandler:
On Error Resume Next
' MsgBox Err.Number & ": " & Err.Description
' GoTo exitHandler
End Sub
ScreenShot001.jpg
Bookmarks