Retrieving information from external workbook from current open workbook using a user form

Question

I code for userform that allows me to retrieve information using project ID within the Excel sheet of the workbook.

However, now I would need to search based on the user input for the projectID found in the external workbook called active master file.

It will then retrieve the information and put it into the userform and use the add command button to insert the information into the new rows found in the current workbook called project tracker file.

The problem is I am not sure of the code to retrieve the information from external workbook to the userform that is present in the current open workbook.

I have two workbooks. This userform is found in a workbook called project tracker while the external workbook that I would like to retrieve the information based on their Project ID is called active master project.

This is the code I have for the command search button for me to do a retrieve and search within a sheet of the workbook called project tracker:

Private Sub CommandSearchButton2_Click()

    Dim lastrow
    Dim ProjCode As String
    Dim LabelProjName As String
    Dim LabelObjective As String
    Dim LabelProjSponsor As String
    Dim LabelProjSponsorNew As String
    Dim LabelProjManager As String
    Dim LabelRegulatory As String
    Dim LabelRiskLvl As String
    Dim LabelDatePar As Date
    Dim LabelCostPar As Long
    Dim LabelAffectCust As String
    Dim LabelCustNonRetail As String
    Dim LabelCustRetail As String
    Dim LabOutsourcingImp As String
    Dim LabelKeyUpdate As String
    Dim LabelSector As String



    searchRow = 0
        lastrow = Sheets("Program Status Summary").Range("B" & Rows.Count).End(xlUp).Row
        ProjCode = TextBoxProjCode.Text

     For currentrow = 4 To 100

        If Cells(currentrow, 2).Text = ProjCode Then

            searchRow = currentrow

            TextBoxProjCode.Text = Cells(currentrow, 2).Text
            TextBoxProjName.Text = Cells(currentrow, 3)
            TextBoxSector.Text = Cells(currentrow, 4)
            TextBoxObjective.Text = Cells(currentrow, 5)
            TextBoxProjSponsor.Text = Cells(currentrow, 7)
            TextBoxProjSponsorNew.Text = Cells(currentrow, 8)
            TextBoxProjM.Text = Cells(currentrow, 6)
            TextBoxRegulatory.Text = Cells(currentrow, 20)
            TextBoxRiskLvl.Text = Cells(currentrow, 13)
            TextBoxDatePar.Text = Cells(currentrow, 12)
            TextBoxCostPar.Text = Cells(currentrow, 10)
            TextBoxAffectCust.Text = Cells(currentrow, 15)
            TextBoxCustNonRetail.Text = Cells(currentrow, 16)
            TextBoxCustRetail.Text = Cells(currentrow, 17)
            TextBoxOutsourcingImp.Text = Cells(currentrow, 19)
            TextBoxKeyUpdate.Text = Cells(currentrow, 18)

        End If

    Next currentrow

TextBoxProjCode.SetFocus

End Sub

R3uK R3uK · Accepted Answer · 2015-10-23T07:53:29

Best way to work with multiple workbooks, is to use object variables :

Dim WbPT As Workbook, _
    WbAMP As Workbook, _
    WsPSS As Worksheet, _
    Ws As Worksheet

'----The SET keyword is only to attribute value to an object variable
Set WbPT = Workbooks("project tracker")
Set WbAMP = Workbooks("active master project")

Set WsPSS = WbPT.Sheets("Program Status Summary")

'----Use with to have the reference availaible starting with a simple dot "."
With WsPSS
    MsgBox .Cells(1, "B")
End With

So if I understood well, your code should be like this :

Private Sub CommandSearchButton2_Click()

    Dim lastrow
    Dim ProjCode As String
    Dim LabelProjName As String
    Dim LabelObjective As String
    Dim LabelProjSponsor As String
    Dim LabelProjSponsorNew As String
    Dim LabelProjManager As String
    Dim LabelRegulatory As String
    Dim LabelRiskLvl As String
    Dim LabelDatePar As Date
    Dim LabelCostPar As Long
    Dim LabelAffectCust As String
    Dim LabelCustNonRetail As String
    Dim LabelCustRetail As String
    Dim LabOutsourcingImp As String
    Dim LabelKeyUpdate As String
    Dim LabelSector As String

    Dim WbPT As Workbook, _
        WbAMP As Workbook, _
        WsPSS As Worksheet, _
        Ws As Worksheet

    Set WbPT = Workbooks("project tracker")
    Set WbAMP = Workbooks("active master project")

    Set WsPSS = WbPT.Sheets("Program Status Summary")

    With WsPSS
        searchRow = 0
        lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
        ProjCode = TextBoxProjCode.Text

         For currentrow = 4 To lastrow
            If .Cells(currentrow, 2).Text = ProjCode Then
                searchRow = currentrow

                TextBoxProjCode.Text = .Cells(currentrow, 2).Text
                TextBoxProjName.Text = .Cells(currentrow, 3)
                TextBoxSector.Text = .Cells(currentrow, 4)
                TextBoxObjective.Text = .Cells(currentrow, 5)
                TextBoxProjSponsor.Text = .Cells(currentrow, 7)
                TextBoxProjSponsorNew.Text = .Cells(currentrow, 8)
                TextBoxProjM.Text = .Cells(currentrow, 6)
                TextBoxRegulatory.Text = .Cells(currentrow, 20)
                TextBoxRiskLvl.Text = .Cells(currentrow, 13)
                TextBoxDatePar.Text = .Cells(currentrow, 12)
                TextBoxCostPar.Text = .Cells(currentrow, 10)
                TextBoxAffectCust.Text = .Cells(currentrow, 15)
                TextBoxCustNonRetail.Text = .Cells(currentrow, 16)
                TextBoxCustRetail.Text = .Cells(currentrow, 17)
                TextBoxOutsourcingImp.Text = .Cells(currentrow, 19)
                TextBoxKeyUpdate.Text = .Cells(currentrow, 18)
            End If
        Next currentrow
    End With

    TextBoxProjCode.SetFocus

End Sub

Retrieving information from external workbook from current open workbook using a user form

1 Answers