Hello

What I am looking to do is give the end-user an option to open a file by typing in the file name in a specific cell (say b10:) My end-users deal with creating and referencing contracts. So, if they need to open a contract, all they would have to do is type in the contract # (which is the file name) and have it opened. Is this possible to do with a user-form or somehow create a macro from this. All the contracts are located on a share drive, but they are saved in different folders. Is it possible to have the macro search all folders for this file or would they all have to be under one folder?

look at the help on the filesearch object. You could trigger that on the
worksheet_change event, testing for the entry cell. Then if it is found,
open it.

You can also use Dir or the scripting runtime library (filesystem object).
Having all files in a single directory would make things simpler, but isn't
essential.

--
Regards,

Tom Ogilvy


"Brad" wrote in message
...
Hello,

What I am looking to do is give the end-user an option to open a file by
typing in the file name in a specific cell (say b10:) My end-users deal with
creating and referencing contracts. So, if they need to open a contract, all
they would have to do is type in the contract # (which is the file name) and
have it opened. Is this possible to do with a user-form or somehow create a
macro from this. All the contracts are located on a share drive, but they
are saved in different folders. Is it possible to have the macro search all
folders for this file or would they all have to be under one folder?

Hi Brad,

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B10")) Is Nothing Then
With Target
Workbooks.Open Filename:=.Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

To input this, right-click on the sheet tab, select View Menu from the menu,
and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Brad" wrote in message
...
Hello,

What I am looking to do is give the end-user an option to open a file by
typing in the file name in a specific cell (say b10:) My end-users deal with
creating and referencing contracts. So, if they need to open a contract, all
they would have to do is type in the contract # (which is the file name) and
have it opened. Is this possible to do with a user-form or somehow create a
macro from this. All the contracts are located on a share drive, but they
are saved in different folders. Is it possible to have the macro search all
folders for this file or would they all have to be under one folder?