Getting data from two worksheets into combobox of userform

[syp1677]

Dear Gurus,

I want to get dropdown list of data which is from two different work sheets.
Right now, I am using following code to get dropdown list from one worksheet:

Private Sub cmbreagid_DropButtonClick()
    Dim i As Long, LastRow As Long
    LastRow = Sheets("Database").Range("A" & Rows.Count).End(xlUp).Row
    If cmbreagid.ListCount = 0 Then
        For i = 1 To LastRow
        cmbreagid.AddItem Sheets("Database").Cells(i, "A").value
        Next i
    End If
End Sub

Now I want to include data of column A from worksheet Database-2 also along with worksheet Database.
Is there a way to do that?

 

[rlv01]

What about something like this?
(not tested)

Private Sub cmbreagid_DropButtonClick()
    Dim i As Long, LastRow As Long
    Dim WS As Worksheet

    For Each WS In Worksheets
        Select Case WS.Name
        Case "Database", "Database-2"
            LastRow = WS.Range("A" & Rows.Count).End(xlUp).Row
            If cmbreagid.ListCount = 0 Then
                For i = 1 To LastRow
                    cmbreagid.AddItem WS.Cells(i, "A").Value
                Next i
            End If
        End Select
    Next WS
End Sub

 

[syp1677]

What about something like this?
(not tested)

Private Sub cmbreagid_DropButtonClick()
    Dim i As Long, LastRow As Long
    Dim WS As Worksheet

    For Each WS In Worksheets
        Select Case WS.Name
        Case "Database", "Database-2"
            LastRow = WS.Range("A" & Rows.Count).End(xlUp).Row
            If cmbreagid.ListCount = 0 Then
                For i = 1 To LastRow
                    cmbreagid.AddItem WS.Cells(i, "A").Value
                Next i
            End If
        End Select
    Next WS
End Sub

Hello riv01,
Thanks for the reply. Its not working it only displays data from database worksheet.
Regards,
S

 

[rlv01]

Hello riv01,
Thanks for the reply. Its not working it only displays data from database worksheet.
Regards,
S

1. Is there a "Database-2" worksheet in your workbook? (exact spelling matters here)
2. Does does the "Database-2" worksheet have any data in column A?

 

[rlv01]

Ok, I see that If cmbreagid.ListCount = 0 Then is causing problems when multiple worksheets are involved.
Try this instead:

Private Sub cmbreagid_DropButtonClick()
    Dim i As Long, LastRow As Long
    Dim WS As Worksheet

    cmbreagid.Clear
    For Each WS In Worksheets
        Select Case WS.Name
        Case "Database", "Database-2"
            LastRow = WS.Range("A" & WS.Rows.Count).End(xlUp).Row
            '''Debug code begin
            Debug.Print WS.Name
            '''Debug code end
            For i = 1 To LastRow
                cmbreagid.AddItem WS.Cells(i, "A").Value
            Next i
        End Select
    Next WS
End Sub

My example now clears the combobox each time and loads fresh data from the worksheets. If that is not how you want it to behave you will have to think about it and articulate a new vision.