Forcing a search on opening a workbook.

[JulianB]

Using Excel 2002. Have created an excel workbook, which enables 12 user
to select a 'new' account number from an alphabetical listing, there i
a spreadsheet for each letter of the alphabet. Macros ensure eas
navigation from one page to another.

One of the requirements, is that a user must search, (Edit > Find) b
name, to ensure that they are not adding a duplicate.

At present this is done on trust. I would like to 'force/push' thi
process when the workbook is opened up by a user. I tried to set up
macro to do this, but it will not work. Does anyone have any othe
ideas

 

[DNF Karran]

providing they are forced to open the workbook with macros enable
something like this will do the job though you may need to scan throug
the multiple worksheets.

Duncan

Sub RunOnOpen()

Dim AccNbr As Long, Duplicate

On Error Resume Next

ReStart:

AccNbr = InputBox("Please enter account number")

If AccNbr = Empty Then
ThisWorkbook.Close
End If

Duplicate = Range(Cells.Find(What:=AccNbr, After:=Range("a1")
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns
SearchDirection:=xlNext, _
MatchCase:=False).Address).Value

If Not Duplicate = Empty Then
If MsgBox("Duplicate account number- Try again?", vbYesNo) = vbYe
Then GoTo ReStart
ThisWorkbook.Close
End If

End Su