Something like this?
Option Explicit
Public Function FindLM() As String
Dim objExcel As Object ' Excel.Application
Dim exWb As Object ' Excel.Workbook
Dim ExcelFileName As String
Dim LM As String
Dim Result As String
ExcelFileName = "U:\Project Support\Local Database\TortMattersLitigation.xlsx"
LM = InputBox("Enter LM#")
On Error Resume Next
Set objExcel = GetObject(, "excel.application")
On Error GoTo 0
If objExcel Is Nothing Then Set objExcel = New Excel.Application
Set exWb = objExcel.Workbooks.Open(ExcelFileName)
Result = exWb.Worksheets("Sheet1").Range("A:A").Find(LM).Offset(, 1).Value
MsgBox = Result
FindLM = Result
End Function
Private Sub Test_FindLM()
Dim X
X = FindLM
End Sub
OR, Something like this:
Option Explicit
Public Function FindLM(Optional LM As String) As String
If LM = "" Then
MsgBox "Must use proper LM Number"
Exit Function
End If
Dim objExcel As Object ' Excel.Application
Dim exWb As Object ' Excel.Workbook
Dim ExcelFileName As String
Dim Result As String
ExcelFileName = "U:\Project Support\Local Database\TortMattersLitigation.objExcelsx"
On Error Resume Next
Set objExcel = GetObject(, "excel.application")
On Error GoTo 0
If objExcel Is Nothing Then Set objExcel = New Excel.Application
Set exWb = objExcel.Workbooks.Open(ExcelFileName)
Result = exWb.Worksheets("Sheet1").Range("A:A").Find(LM).Offset(0, 1).Value
MsgBox = Result
FindLM = Result
End Function
Private Function WhichLM() As String
Dim LM As String
SartOver:
LM = InputBox("Enter LM#")
If Len(LM) <> nn Then GotTo StartOver
If CLng(LM) < nnnn Or CLng(LM) > nnnn Then GoTo StartOver
'More Tests As desired
'If all tests pass then...
WhichLM = LM
End Function
Private Sub Test_WhichLM()
Dim X
X = WhichLM
End Sub
Private Sub Test_FindLM()
Dim X
X = FindLM(WhichLM)
End Sub