Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    user interaction (2003)

    I set out below the bit of code that needs amendment. Currently the list of suppliers within the select case is hard coded in VBA.

    I need to find a way of allowing users to add or delete suppliers form this list on line 2. Can anyone please assist?
    Line
    1 Select Case Supplier
    2 Case "HEWLETTPA", "DIGITAL", "SIEMENS", "IBMUK", "DELL"
    3 Cells(j, 20).Value = Cells(j, 19).Value & Cells(j, 13).Value
    4 Case Else
    5 Cells(j, 20).Value = Cells(j, 19).Value & "Other"
    6 End Select

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: user interaction (2003)

    If you want it hard-coded, the users must edit the code (not advisable)

    You could make the list in column A of a worksheet (in the example code it is named "list", change as desired). Then use match to find the matching row. If not found, give the "not found response" if found the found response. For example:

    <pre>Dim x As Long
    x = 0
    On Error Resume Next
    x = Application.WorksheetFunction.Match(supplier, _
    Worksheets("List").Columns("a"), 0)
    On Error GoTo 0
    If x = 0 Then
    Cells(j, 20).Value = Cells(j, 19).Value & "Other"
    Else
    Cells(j, 20).Value = Cells(j, 19).Value & Cells(j, 13).Value
    End If</pre>


    You also get the row number (x) in the code, in case you want to include other info in the table (in othte columns) so that the code could get that also

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user interaction (2003)

    Thanks Steve, that works well with a column in sheet list.

    Instead of using Columns("A") I tried to use a named range, but with:

    x =Application.WorkSheetFunction.Match(Supplier, WorkSheets("List").Range("SupplierList"),0)

    x was always returned as 0

    Is there something wrong with the syntax?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: user interaction (2003)

    Make sure that there is no typo in any of the names. The code works OK for me in a test workbook (in Excel 2002)

  5. #5
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    Louisville, Kentucky, USA
    Posts
    134
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: user interaction (2003)

    Make sure that "SupplierList" refers to a 1 dimensional range of cells. Match doesn't work if your range is 2 dimensional.

Posting Permissions

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