How to create a "goto" box?

[touque]

I have a sheet listing over 200 individuals and their balances. When I want to look at someone's balance with a name such as Wood I have to scroll down over 200 rows.
Can I have box on one of the top rows where I can enter the starting letter and the sheet will automatically go to the first cell starting with the entered letter.

 

[alansidman]

Why not use the built in Find function that is on the Ribbon on the Home Tab at the far right.

 

[touque]

For sure that would work but looking for something a little "prettier"!
Something that is right on the page.
I tried to see if I could somehow have the "Find" function permanently open as that would suffice. I have room to the side.
Any other options?

 

[jproffer]

Is VBA an option? We can either create a userform and have it show automatically when you open the workbook....or....code it to open the "Find" dialog when you open the workbook.

 

[touque]

I'm a little green in regards to code. Created a couple with help from this form. But willing to learn.
How would you suggest to set this up?

 

[jproffer]

Depends on which one you prefer. Opening the "find" dialog when you select that worksheet is as easy as:

-Rick click on the tab of that sheet.
-Click "View Code"
-Paste the following into the white area on the right.

Private Sub Worksheet_Activate()

Application.Dialogs(xlDialogFormulaFind).Show

End Sub

A userform would be a little more work, but still doable.

And it would naturally go away before you change tabs (or change anything else for that matter) because that dialog is non-modal (I think it's "non")...either way, it takes over everything until you close it.

 

[My Aswer Is This]

How about selecting the name from a Combobox.
Assuming the list of names are in column A
And the balances are in column B
We could have a script display the balance in a Message Box
Not needing to go down the sheet to see the balance


Create a activex Combobox named Combobox1
Create a activex Command button named Command Button 1

The command button will load all the names in Column A into the Combobox
Then when you select a name from the combobox you will get a message box displaying the balance

And install these scripts in those buttons:

Private Sub ComboBox1_Click()
Dim SearchString As String
Dim SearchRange As Range
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
SearchString = ComboBox1.Value
Set SearchRange = Range("A1:A" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
If SearchRange Is Nothing Then MsgBox "Not Found"
ans = SearchRange.Offset(, 1).Value
MsgBox SearchString & "  Balance is  " & ans
End Sub

Private Sub CommandButton1_Click()
ComboBox1.Clear
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
ComboBox1.List = Range("A1:A" & Lastrow).Value
End Sub

 

[touque]

That was nice and simple and suffices my need!
Thanks Jproffer

 

[jproffer]

Glad that worked for you. Thanks for the feedback.

 

[My Aswer Is This]

Depends on which one you prefer. Opening the "find" dialog when you select that worksheet is as easy as:

-Rick click on the tab of that sheet.
-Click "View Code"
-Paste the following into the white area on the right.

Private Sub Worksheet_Activate()

Application.Dialogs(xlDialogFormulaFind).Show

End Sub

A userform would be a little more work, but still doable.

And it would naturally go away before you change tabs (or change anything else for that matter) because that dialog is non-modal (I think it's "non")...either way, it takes over everything until you close it.

A Userform can be open and you still be able to work on your worksheet.
Open your userform like this:
UserForm1.Show Modeless