Input box query

[Greg B]

I am hoping to use a inputbox to have the user type in the 6 digit number
each client has been assigned. I would like for the ability for excel to see
this number and go to the corresponding sheet with this number.

The number is place in cell "d1"

Is this possible

TIA

Greg

[T-Žex]

One way to do this is to navigate through the sheets and check cell D1 in each sheet if the value is equal to the specified number....

Please Login or Register  to view this content.

I am hoping to use a inputbox to have the user type in the 6 digit number
each client has been assigned. I would like for the ability for excel to see
this number and go to the corresponding sheet with this number.

The number is place in cell "d1"

Is this possible

TIA

Greg

[Ken Johnson]

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