LookupList on a different workbook RRS feed

  • Question

  • Hi I was trying to create a simple excel program that runs on macro. Is there a way that I can have my lookuplist on a different workbook so that if i need to do some changes on my data i dont have to go through all my files to change it. My lookup list was originally located on the same workbook. Below is the original code.

    Private Sub UserForm_Initialize()
    Dim cPart As Range
    Dim cLoc As Range
    Dim ws As Worksheets
    Set ws = Worksheets("LookupLists")

    For Each cPart In ws.Range("PartIDList")
      With Me.cboPart
        .AddItem cPart.Value
        .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
      End With
    Next cPart

    For Each cLoc In ws.Range("LocationList")
      With Me.cboLocation
        .AddItem cLoc.Value
      End With
    Next cLoc

    Me.txtDate.Value = Format(Date, "Medium Date")
    Me.txtQty.Value = 1
    End Sub

    Can anybody help me change the code so that the lookuplist will be on, let say i named the workbook PriceUpdater.

    Monday, October 31, 2011 7:53 AM


  • Dim ws As Worksheet ' not WorksheetS
    Dim wb As Workbook
    Dim sName as String

    ' some code to assign sName
    sName = "PriceUpdater"

    Set wb = Workbooks(sName)
    Se ws = wb.Worksheets("sheet-name")

    As your code is in a Userform I doubt you'd want to hardcode the workbook name in the Initialize event. Depending on needs maybe populate a list (combo or ListBox) with potential names in the Initialize event, then  when user selects the name similar code from the combo's change event fires. Or, maybe pick up the name from say a cell in the activesheet. There are lots of ways.

    Peter Thornton

    Monday, October 31, 2011 8:35 AM