Results 1 to 5 of 5

Drop down list in order form doesn't work when customer info cells are filled out

Threaded View

nokamoto Drop down list in order form... 08-27-2014, 07:03 PM
Danerida Re: Drop down list in order... 08-27-2014, 08:05 PM
nokamoto Re: Drop down list in order... 08-28-2014, 03:49 PM
Danerida Re: Drop down list in order... 08-28-2014, 07:51 PM
nokamoto Re: Drop down list in order... 09-16-2014, 03:53 PM
  1. #1
    Registered User
    Join Date
    04-28-2011
    Location
    Seattle WA
    MS-Off Ver
    Excel 2007
    Posts
    4

    Drop down list in order form doesn't work when customer info cells are filled out

    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
    Last edited by JBeaucaire; 08-27-2014 at 07:31 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto Populate Order Form, based on qty's filled in Price list - Query
    By magpie10 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-11-2014, 02:08 AM
  2. Updating a pivot table with VBA from Drop down list (1st item doesn't work)
    By tray262 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-25-2013, 02:32 PM
  3. VBA code for (un)hide columns related to drop down list doesn't work
    By maumon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-21-2010, 08:27 AM
  4. Purchase Order Customer Info Autopopulated
    By lj123 in forum Excel General
    Replies: 5
    Last Post: 09-29-2008, 05:00 PM
  5. Order form drop down list issue
    By TampaPromoGirl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-14-2008, 03:41 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1