Input box query
 

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

I would do this with putting the code below to the sheet where you have
the cell "d1".

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo EXITSUB:
Select Case Range("$D$1").Value
Case "1"
Sheets(2).Select
Case "2"
Sheets(3).Select
End Select
EXITSUB:
End Sub

R,
mas


--
mas_detokyo


------------------------------------------------------------------------
mas_detokyo's Profile: http://www.excelforum.com/member.php...o&userid=26712
View this thread: http://www.excelforum.com/showthread...hreadid=401387

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