Activate the other file depending on the input value from the input button

Here is a routine that will search in a user selected directory for all files (.xls) in sheet1
cell C1 (change to suit).
It will compare against your value = MyValue
variable.
Note: it stores the results in 1 cell = C1.
You will have to adapt to suit.

Option Explicit
Public i As Integer
Public Drive As String
Public Ans As Integer
Public FFiles As Integer
Public WB As Integer
Public FileNm() As String
Public K As Integer
Public temp As String
Public MyValue

Public Type BROWSEINFO
hOwner As Long
pid1Root As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Declare Function SHGetPathFromIdList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" (ByVal pid1 As Long, ByVal pszPath As String) As Long

Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long

Function GetDirectory(Optional msg) As String
Dim bInfo As BROWSEINFO
Dim path As String
Dim r As Long, x As Long, pos As Integer

bInfo.pid1Root = 0&

If IsMissing(msg) Then
bInfo.lpszTitle = "Select a folder"
Else
bInfo.lpszTitle = msg
End If

bInfo.ulFlags = &H1

x = SHBrowseForFolder(bInfo)

path = Space$(512)
r = SHGetPathFromIdList(ByVal x, ByVal path)
If r Then
pos = InStr(path, Chr$(0))
GetDirectory = Left(path, pos - 1)
Else
GetDirectory = ""
End If
End Function

Sub OpenFile_NumberInput()

'Define the value to compare here!
MyValue = 14
Start1:
'----------------------------------------------------------------------------
Drive = GetDirectory("Select the directory of the files to look@")
If Drive = "" Then End
Ans = MsgBox(Drive, vbYesNo, "Load @ files in this Directory?")
If Ans = vbNo Then GoTo Start1
'-----------------------------------------------------------------------------

With Application.FileSearch
.NewSearch
.LookIn = Drive
.SearchSubFolders = False
.Filename = "*.xls"
.MatchTextExactly = True
.MatchAllWordForms = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
ReDim FileNm(.FoundFiles.Count)
For i = 1 To .FoundFiles.Count
K = 0
FileNm(i) = .FoundFiles(i)
temp = ""
Do Until temp = "\"
temp = Mid(.FoundFiles(i), Len(.FoundFiles(i)) - K, 1)
K = K + 1
Loop
FileNm(i) = Right(.FoundFiles(i), K - 1)
Next
End If
If .FoundFiles.Count = 0 Then MsgBox "No files in " & Drive: End
End With

'In case sheet doesn't exist
On Error GoTo ErrH
Application.ScreenUpdating = False
Application.EnableEvents = False
'Reset original val
Range("C1").FormulaR1C1 = ""
For WB = 1 To UBound(FileNm())
Range("C1").FormulaR1C1 = "='" & Drive & "\[" & FileNm(WB) & "]" & "Sheet1'!R1C3"
Range("C1").Calculate
'In case of Type mismatch
On Error Resume Next
If Range("C1") = MyValue Then
Workbooks.Open Drive & "\" & FileNm(WB)
End If
On Error GoTo 0
Next

Application.ScreenUpdating = True
Application.EnableEvents = True
MsgBox "Completed! looking @ " & WB - 1 & " Workbooks"

Exit Sub
ErrH:
If Err.Number <> 1004 Then
MsgBox "Error#:=" & Err.Number & " :=" & Err.Description
Else
'Sheet1 doesn't exist
Resume Next
End If

End Sub

HTH

Ivan

You can do this without openning the files.
BUT do you want to search everysheet @ the same
location ???
The answer is a little complicated TOO.

Ivan

Hi Ivan,
Thanks for your response.. Yes, I need to search for every workbook(.xls files) at the same directory. The cell would be the same cell for every workbook(for example workbook1 sheet1 C2 and workbook2 sheet1 C2).
Thanks!