No we can do better than that.

What we do is add application events to trap every workbook being opened. On
open, we ask if this is it, if so we get workbook automatically. This
workbook object will be stored in thisWb, so just use thisWb when
referencing the data thereafter.

Firstly, all of this code goes in the designated workbook.

'========================================
In a standard code module, declare a public variable of thisWB

Public thisWB as Workbook

'========================================
Insert a class module, rename it to 'clsAppEvents', with this code

Option Explicit

Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
Dim ans

ans = InputBox("Is the file?", vbYesNo)
If ans = vbYes Then
thisWB = ActiveWorkbook
End If

End Sub
'========================================
In ThisWorkbook code module, add this event code

Dim AppClass As New clsAppEvents

Private Sub Workbook_Open()

Set AppClass.App = Application

End Sub


--

HTH

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

"pauluk " wrote in message
...
The user will know the differnece.

I was thinking of placeing an input box at the start for the user to
input the file name. But then it really does depend on the user placing
the correct name otherwise the coding will not work.


---
Message posted from http://www.ExcelForum.com/