Need help writing a macro

[hawkeye71]

Hi,

I have five columns in a work sheet. one column has telephone number, another has name of the person, the other 3 coluns have different information.
I want to write a macro, so that it asks for the phone number in a pop-up box, looks for that number in the whole excel file, and if it finds it, get the name of person from the cell (Bn) and displays it in a pop-up text box. n is the number of cell.
If the phone number is not in the list, it displays "Not found".
I prefer that the when the user clicks on the file, it just runs the macro and does not display the contents of the file. However, the file admin should be able to modify the contents of the file.
I have never written a macro in my life, so all the help is appreciated.
I do have some VB experience.

Thanks,
Indiana

 

[SkipVought]

Hi,

Have you considered using the AutoFilter feature to lookup the phone number?

Skip,
[sub]
[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it! [/sub]

 

[VBAjedi]

As long as your telephone numbers are in the first column of your data, one approach would be to use VLOOKUP formulas.

Select your whole data range and name it something like 'MyData'. Now hide this sheet, using a password that you will give to your administrator.
Next, insert a new sheet into your workbook. Set up one cell that the user can type the phone number into. Let's say you used cell B4. In the cell you want the name to appear in, type this formula:

=VLOOKUP(B4, MyData, 2)

Play around with it and see if you'd like to work with this approach, then post back if you have further questions. . . Also read the help file entry on VLOOKUP formulas.

VBAjedi