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

Input box query
 

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....


Code:
--------------------
Sub GotoNumber()
Dim SixDigit As String
Dim SheetCount As Integer
Dim SheetIndex As Integer
Dim QuerySheet As Worksheet

SixDigit = InputBox("Enter 6-digit number:", "What's the number")
SheetCount = Sheets.Count

For SheetIndex = 1 To SheetCount
Set QuerySheet = Worksheets(SheetIndex)

If QuerySheet.Range("D1").Value = SixDigit Then
QuerySheet.Activate
QuerySheet.Range("D1").Activate
Exit Sub
End If
Next SheetIndex

MsgBox "Cannot find '" & SixDigit & "'.", vbInformation, "Not Found"
End Sub
--------------------




Greg B Wrote:
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
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=401390

Input box query
 

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