Pull Down List using data from multi sources

Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Pull Down List using data from multi sources

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Location
    Here and There
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pull Down List using data from multi sources

     
    I need to make a pulldown list of what is essentially 11 digit codes that are listed on different tabs. There are some duplicates. In the end I want to place this pull down list in a User Form. Any ideas? Thanks.

    Snowshoeken
    "Realizing you have nothing, allows you to do anything."

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Down List using data from multi sources

    What are the names of the sheets and in which cells on each sheet are the codes?

  3. #3
    Board Regular
    Join Date
    Aug 2002
    Location
    Here and There
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Down List using data from multi sources

    Names of the sheets are

    AL Zones, FL Zones and GA Zones. The data is located in the first 5 colums with varying numbers of rows. Thanks

    Snoshoeken

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Down List using data from multi sources

    Are the codes in all 5 columns or just in eg column A?

  5. #5
    Board Regular
    Join Date
    Aug 2002
    Location
    Here and There
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Down List using data from multi sources

    Sorry, yes the 11 digit codes are in colmn A.

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Down List using data from multi sources

    This will fill ComboBox1 with unique items:

    Code:
    Private Sub UserForm_Initialize()
        Dim Codes As New Collection
        Dim Rng As Range
        Dim c As Range
        Dim i As Long
        Dim j As Long
        Dim Temp As Variant
        With Worksheets("AL Zones")
            Set Rng = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
            On Error Resume Next
            For Each c In Rng
                Codes.Add c.Value, CStr(c.Value)
            Next c
            On Error GoTo 0
        End With
        With Worksheets("FL Zones")
            Set Rng = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
            On Error Resume Next
            For Each c In Rng
                Codes.Add c.Value, CStr(c.Value)
            Next c
            On Error GoTo 0
        End With
        With Worksheets("GA Zones")
            Set Rng = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
            On Error Resume Next
            For Each c In Rng
                Codes.Add c.Value, CStr(c.Value)
            Next c
            On Error GoTo 0
        End With
        If Codes.Count > 1 Then
            For i = 1 To Codes.Count - 1
                For j = i + 1 To Codes.Count
                    If Codes(i) > Codes(j) Then
                        Temp = Codes(j)
                        Codes.Remove (j)
                        Codes.Add Temp, CStr(Temp), i
                    End If
                Next j
            Next i
        End If
        For i = 1 To Codes.Count
            ComboBox1.AddItem Codes(i)
        Next i
    End Sub

  7. #7
    Board Regular
    Join Date
    Aug 2002
    Location
    Here and There
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Down List using data from multi sources

    So far it is working really well. Was easy to add additional sheets. Thanks you.

    Was wondering, would this work in a User Form?

    Thanks again.

    Snowshoeken.

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Down List using data from multi sources

    My code was for a UserForm - that's why I used the UserForm_Initialize event procedure.

  9. #9
    Board Regular
    Join Date
    Aug 2002
    Location
    Here and There
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Down List using data from multi sources

    Another Q. The header for the 11 digit codes isn't "codes". It is actually "Switches", should I change all the ref. to "codes" to the colmn header? Thanks again.

    Snowshoeken

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pull Down List using data from multi sources

      
    No, Codes is just a name I gave the Collection. If you want to exclude the header row from the list, change:

    Code:
    Set Rng = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
    to

    Code:
    Set Rng = .Range("A2:A" & .Range("A65536").End(xlUp).Row)

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com