Hi all,

I am trying to code a function into a userform whereby when someone enters an account number (accountnumber_Change()) it searches a worksheet for that account number and if found pulls the order number requirement (which is in the cell next to it) from that worksheet and displays it in the userform.

I have code that is working but it searches a worksheet in the same workbook as the userform. I am wanting to change it so it searches a worksheet in another workbook.

The second workbook I have is called 'Authorisation_Log.xlsx' and the worksheet is called 'Order Format'

Any help is greatly appreciated.


Private Sub AccountNumber_Change()
'loop thru account numbers and find order number format

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Order Format")

wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row

For x = 2 To wsLR
    If ws.Cells(x, 1) = Me.AccountNumber Then
        'get order number
        Me.OrdeRequirement = ws.Cells(x, "B")
        Exit Sub
        
    End If
    
Next x

End Sub