Quick Telephone Book Lookup

[cfer]

Did a search for, but nothing seemed to be what I was looking for.

I have a spreadsheet that is oen during my shift, I would like to have a button, when pressed, a box opens up, you type in the name you want, hit OK, the number then appears.

Any suggestions please.

cfer

 

[Norie]

Is it a spreadsheet or a database?

 

[cfer]

Thanks for the reply, Norie

At the moment there are several papers pinned on the wall, and a dairy on the desk.

My idea was to put all the numbers and names onto a sheet, thus having an easy way of getting the number.

Whether it is a spreadsheet or database, I am open to suggestions as I would type the details as required.

What would the simplest and easiest way would be great.

Thanks cfer

 

[SydneyGeek]

Let's assume you want to use a database. This is one way to do it:

Single table, Contacts
Fields as required: ID, FullName, WorkPhone, Mobile, any others that you need. ID is an Autonumber, key field. (Required in case you have 2 contacts with the same name).

You'll need to build a search form. Do this:
New form. Unbound (ie NOT connected to a table or query). Name it frmSearch and save it.
On this form place a Combo box (cmbName), and a separate TextBox for each detail that you want to see. (say, txtWorkPhone and txtMobile). Place the Combo with the Wizard activated and it will do most of the grunt work for you. You want the Combo to do the following:

1. Look up values in a Table or Query (Contacts)
2. Use the fields ID, FullName and (if the Wizard allows), WorkPhone and Mobile.
3. Hide the key column.

Now you need some code to push the desired values into the text boxes.

In Design view, right-click the Combo and select Properties. Click the Events tab, and double-click the white space to the right of After Update.
You'll see [Event Procedure] in the space. Click the builder (...) button and you'll see a code screen. The cursor will be in between Sub cmbName_AfterUpdate and End Sub.
Type or paste this code:

[txtWorkPhone]=Column(2).Value
[txtMobile]=Column(3).Value

If you used different names for your controls, adjust the names to suit.

Note: the first column in a combo is 0. This is the key field: 1 is the Name, 2 is the WorkPhone, etc, based on the order of the fields that you chose when you created the combo box.

Save the form and give it a whirl. All going well, when you type in the Combo, it will quickly narrow the search down to the name you want. When you hit the [TAB] key, the details for that person will appear on the form.

HTH
Denis

 

[cfer]

Thanks Sydneygeek,

Can the Database be linked to the Excel sheet, as we have the Excel file open at all times, data is manipulated on a sheet. So I was lookng at havng a button on the excel sheet, which would open to allow me the telephone query. If not can it be done by excel.

Thanks cfer

 

[SydneyGeek]

Can the Database be linked to the Excel sheet, as we have the Excel file open at all times, data is manipulated on a sheet. So I was lookng at havng a button on the excel sheet, which would open to allow me the telephone query. If not can it be done by excel.

Yes, it can. In Access, create a blank database. File | Get External Data | Link... and point at the Excel file. It then behaves like an Access data source -- except it will be a bit slower than Access tables. The advantage is that you can continue to update the spreadsheet and Access will see the changes.

An alternative is to build a form in Excel, which launches when you click the button. You can get it to do much the same as I described for Access, but it's a bit more clunky.

Denis

 

[khexcel]

Hi,
If your table is hugh and you want to return a lot of values, a database is probably
your best bet. However, if it is something simple, you can put a button in Excel.

Try this in Excel:

Private Sub CommandButton1_Click()
Dim inputvalue As String 'This string will use to store the search value
Dim search_again As String
inputvalue = InputBox("Enter Name", "Name Search") 'message box that prompts for search value
search_again = vbYes

Do Until search_again = vbNo 'continue search unitl user press no

On Error GoTo err_Handler 'In case there is no match then error

Cells.Find(What:=inputvalue, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
.Activate 'This will find the search value
MsgBox ("The phone number for " & inputvalue & " returned " & ActiveCell & " is " & Range("B" & ActiveCell.Row) & _
", cell number is " & Range("C" & ActiveCell.Row)) 'this will return the search

search_again = MsgBox("Do you want to search again?", vbYesNo, "Search Again") 'prompt to search again
Loop

button_exit:
Exit Sub

err_Handler:
MsgBox ("No Name Found") 'message if no match
Resume button_exit

End Sub

This assumes that phone number is in column B and cell number in column C.
You can modify the message box range("B" & activecell.row). Change B or C to the columns
the phone numbers are in.

 

[cfer]

Thanks, I am trying both now.

cfer

 

[cornishteeth]

I thought this would be pretty cool to play around with it, but I am having some difficulty. I keep getting a compile error: Sub or Function not defined. I am new to VB (trying to learn). I named it exactly as SydneyGeek explain and here is the code below.

Private Sub cmbName_AfterUpdate()
[txtWorkphone] = Column(2).Value
[txtMobile] = Column(3).Value

End Sub

The highlighted text falls on Column in the txtWorkphone line. I am using Access 2003. What am I doing wrong. Seems like it would work. I created two unbound textboxes on a unbound form and one combo box just as he explain. This is driving me nuts. Can someone help

Frank

 

[SydneyGeek]

Hi Frank,

send me a PM with your email address and I can send you a sample database that illustrates the concept.

Denis