Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MS Query not installed for New Database Query | Excel Discussion (Misc queries) | |||
Web Query Help... | Excel Discussion (Misc queries) | |||
Input boxes in excel and MS Query | Excel Discussion (Misc queries) | |||
"Query cannot be edited by the Query Wizard" | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |