Hi Greg,
If you're still looking...
if you paste this code into the ThisWorkbook Code area of the
VB
Editor, when the workbook is opened the user is asked for the 6 digit
client number. If the number is not found in D2 on any of the
worksheets a MsgBox asks them to try again. The user can keep getting
it wrong for a total of four failed attempts. After that the workbook
justs opens normally so that the user can then close the workbook.
I hope this is useful.
Option Explicit
Private Sub Workbook_Open()
Dim StrClientNum As String
Dim ObjSht As Worksheet
Dim BinNumberFound As Boolean
Dim AttemptCounter As Byte
BinNumberFound = False
InputNumber: StrClientNum = Application.InputBox( _
Prompt:="Please Enter Your 6 digit Client Number.", _
Title:="Client Number?", Type:=2)
AttemptCounter = AttemptCounter + 1
For Each ObjSht In ActiveWorkbook.Worksheets
If ObjSht.Range("D1") = StrClientNum Then
BinNumberFound = True
ObjSht.Activate
Exit For
End If
Next ObjSht
If BinNumberFound = False And AttemptCounter < 4 Then
MsgBox "Number Not Found!" & vbCrLf & "Try Again"
GoTo InputNumber
End If
End Sub
Ken Johnson