Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
With QUERY how to input a variable | Excel Discussion (Misc queries) | |||
List Box Input to query | Excel Discussion (Misc queries) | |||
Input box query | Excel Discussion (Misc queries) | |||
Input boxes in excel and MS Query | Excel Discussion (Misc queries) | |||
Activating A Query from An Input Box | Excel Programming |