Excel Userform VBA VLOOKUP from intranet file

Question

I have a VBA Vlookup on a user form from which I would like the data to come from an excel file saved on the intranet.

When I try the VLookup which takes the data on the intranet, it works as follows:

=VLOOKUP(I6,'https://intranet.private.private.uk/folder1/folder2/folder3/Documents/[Contract database.xlsm]AvailableContracts'!$H$9:$L$108,2,FALSE)

I would like to incorporate that link into the VBA below without getting an error message:

Private Sub ContractsList_AfterUpdate()

If WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.ContractsList.Value) = 0 Then
    MsgBox "This contract is not on the list"
    Me.ContractsList.Value = ""
    Exit Sub
End If
'Lookup values based on first control
With Me
    .TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, Sheet2.Range("A5:E72"), 2, 0)
End With

End Sub

The line that kicks the error is:

.TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, Sheet2.Range("A5:E72"), 2, 0)

when I change Sheet2.Range("A5:E72") with the intranet link above in the excel VLookup formula.

Answer 1

The below will open the workbook and allow you to work with it like any other workbook. I updated your code to include the new workbook and the range you gave in your VLOOKUP from above. I don't think VBA will let you do the lookup like you are hoping.

Private Sub ContractsList_AfterUpdate()
Dim wb as Workbook

Set wb = Workbooks.Open("https://intranet.private.private.uk/folder1/folder2/folder3/Documents/Contract database.xlsm")
If WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.ContractsList.Value) = 0 Then
    MsgBox "This contract is not on the list"
    Me.ContractsList.Value = ""
    Exit Sub
End If
'Lookup values based on first control
With Me
    .TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, wb.Sheets("AvailableContractsRange").Range("H9:L108"), 2, 0)
End With

End Sub

Answer 2

You could save a lot of time and code line using the Application.Match, look at the code below :

Private Sub ContractsList_AfterUpdate()

Dim WB As Workbook
Dim Sht As Worksheet

' set workbook to workbook location at internet (according to your formula)
Set WB = Workbooks.Open("https://intranet.private.private.uk/folder1/folder2/folder3/Documents/Contract database.xlsm")
Set Sht = WB.Worksheets("AvailableContracts")

With Me.ContractsList
    If Not IsError(Application.Match(.Value, Sht.Range("A:A"), 0)) Then '<-- value found in Column A
         'Lookup values based on first control
        Me.TextBox1 = Sht.Range("C" & Application.Match(.Value, Sheet2.Range("A:A"), 0)).Value
    Else '<-- value not found in Column A
        MsgBox "This contract is not on the list"
        .Value = ""
        Exit Sub
    End If
End With

End Sub

Answer 3

How about this? You should determine the workbook as well.

 Private Sub ContractsList_AfterUpdate()

        If WorksheetFunction.CountIf(Workbooks("Contract database").sheet2.Range("A:A"), Me.ContractsList.Value) = 0 Then
            MsgBox "This contract is not on the list"
            Me.ContractsList.Value = ""
        Exit Sub

        End If
        'Lookup values based on first control
        With Me

            .TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, Workbooks("Contract database").sheet2.Range("A5:E72"), 2, 0)

        End With
 End Sub

Answer 4

It looks like you've commented out your

With Me

line, but that may just be the formatting in your question.

It would be helpful to know which error message you received.