In This video I showing How Can Create Addressbook In Excel You Can Search By Land NO Or Fax NO Or Mobile NO With start Digit By. Simply the Best Excel Phone Book - Contact Manager This is a VBA Excel phone book series that will develop this fantastic phone contact interface. Everything. I have phone book in excel, Now when i search name it searches only first letter starting name but if i put any letter middle name then it doesn't.
|Language:||English, Portuguese, German|
|ePub File Size:||27.54 MB|
|PDF File Size:||13.79 MB|
|Distribution:||Free* [*Registration needed]|
I have Names in column A and telephone numbers in column B I need a userform with two text I cannot see how to run it without opening the VBA window .. com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_ html. I have several contacts (mobile number and email id) in excel sheet. How can i create a contact book with search function by using vba in excel. With this userform ; - Data can be added - Data can be deleted - Data can be edited - Data can be searched on the sheet - Listbox can be filled.
If is somehow possible to change the data that are already in the Worksheet. Like i'm able to introduce the data, to be able to modify it as well in another userform. Phone book in VBA with combo box, multiple names Yes it's possible.
This may help you: Phone book in VBA with combo box, multiple names I do not have any idea how to do that. Would you help me? Thank you! Share it with others Like this thread? Remember Me? Page 1 of 2 1 2 Last Jump to page: Results 1 to 10 of Phone book in VBA with combo box, multiple names. Thread Tools Show Printable Version. Hi everyone, I have a file with a start of a phone book. Welcome to MrExcel. Microsoft MVP - Excel. Thank you very much, that worked very well. When I provide a single name first or last it looks for it in the address book and returns the first and last names of the person it found.
One was a full name which surprisingly returned wrong results. Your mileage may vary. This will not work for any large organization. But otherwise, this is not reliable. You have a few questions: I want to provide the enterprise ID of the person, have it look for that and then return other information location, phone number etc. I do not think this is how Outlook resolves email addresses from an alias. You will need to reference some external database to perform a query like that.
Set the option Break on Unhandled Errors. I would suggest that you read the resent article on error handling on the website VBA for Beginners series. You need to not only add the extra columns and set the number in the properties but also set the widths for the extra columns. Contact me and I will send you a sample file to show how to create a print option. I have tried to make a customize contact book from your videos.
It will be all done to the last.
Subscribe to RSS
Only last 3 codes are not working for me. Edit Contact Button. Delete Button 3. Clear All Button. In this 3 buttons I am getting an error. Error No. And in edit contact code when I am trying to edit the contact. It creates a new contact without generating ID. Hi Jainam, The recommended way is to complete the project as it is set out on the website and then customise it to your needs. The benefits of this approach is that you will be able to see what each piece so code is doing.
I am happy to offer you support for the project but do not have the available time to customise projects to individual needs. Trev Resolved Coding error in work referencing txtID on the form was missing in the work book. Adjusted find range. Issue resolved. Easton, I can't thank you enough. I guess I am the only person from Nepal following you and learning from your awesome website.
I was in Oz for 4 years. I wish i had known about your website then. Anyways, you rock! While I am also enjoying your later work, this application is easier for me to understand at my beginner level. I am using it on what is a big project for me, and as you like to say, it is working a treat!
I can't tell you how much I appreciate your sharing your hard work.
Download excel phone list template
The few bucks I spent downloading this completed project along with your manual not only saved me tons of hours, but I never would have been able to figure this stuff out without it no matter how much time I spent. Hi Kevin, I think you are being modest. Your work is excellent. I really appreciate your input. Cheers Mate. Hi Trevor, I was wonder if I could also add a photo of each contact to this too. I know how to add hyper link in excel cells but not in VBA, not sure if this is possible or not, could you shed a little light on this for me.
Hi Mike, You certainly have found some uses for this application.
How to Clean Up Outlook Contact Phone Numbers using VBA
Again this is not something that I have done but I guess the first thing that needs to be considered is where did you want to play the file? In the application or in Windows media player? It would need to be linked and not embedded. There are some very nice features available in Windows media player for playlists et cetera have you have a look at these? I really think that we may be getting outside the intended purpose for Microsoft Excel.
There are other applications that are designed to store and play media files. Probably the best thing to do is have a search around the web and see if anybody else has done this. Maybe you can get some ideas. There is a very good tutorial on the website about embedding YouTube videos in Microsoft Excel.
I demonstrate the three methods to do this in this article. Hi Mike, This feature is a default feature in Microsoft Access and If you wish to do something like this in Microsoft Excel I think it probably would be feasible. You would need to add another column to your database and in that column you would need to have the name of the picture and the extension. The folder path would be established as in inserting a photo in a user form project.
When you double clicked me staff member in the listbox you would need to run some code that would locate the folder path and the name of the file name in the database and assign it to the image container. Having said that you would need also run some code that would check to see if a picture was available and also to cope with the fact that someone may have deleted a picture or moved it.
This would generate an error. This is not something I have done so I could not recommend it but maybe in the future I could have play around with it.
Hi Thank you for the reply i understand what your saying, mybe im getting just a little carried away now lol. HI Mike, Hyperlinks to web address are not a problem nor is sending an email with an attachment to a recipient.
You would use the double click event for the text box that contains the email address. What you want to do is open the email client dialog box with the email inserted.
This would depend on what email client you are using. Hi, I change the texbox format to double click but still don't show, is there a code I need to put in the too? Sorry Mike I meant that you could use the double click event to run the code. The code below is not want you want but will give you an idea.
Change the textbox name to suit and put it in the double click event. Dialogs xlDialogSendMail. Show Me. One last question lol sorry about all this.. If I wanted the link to point to a document on my drive and not website or email what code would I use for the double click.
I have done the hyperlink just need to code like the one you done for send mail. Hi Mike, When you say you want to point to a document. What do you mean? Do you want to open another document from within Microsoft Excel?
Hi, its not within Excel no, when i duble click i want it to open a pdf doc on my drive, i have done the hyperlink just need to code for the double click. I have sent you a test file. ReturnBoolean ActiveWorkbook. FollowHyperlink Me. Value End Sub. Thank you very much for your excellent tutorials, they have helped me so much. I have created your phone book and it works perfect. Question regarding the Extract and Criteria dynamic ranges on sheet1.
Is it possible to have more instances of this functionality In one workbook? Of course with different range names. Let's say Extractpromo and Criteriapromo on sheet2. The problem I'm facing is when i run my second userform. It renames my dynamic ranges on sheet 2 to Extract and Criteria.
Hi Mike, When an advanced filter runs it creates 2 named ranges one for the extract and one for the criteria in addition to your dynamic named ranges. These change with the data presented. They will not effect the dynamic named ranges. To view a dynamic named ranges you need to go to the name manager as they is a formulas. You will see the extract and criteria ranges as static in the name box at the top left of the sheet. You will see all of the named ranges in the name manager.
You can run multiple criteria blocks or multiple criteria with in the criteria range. It is extremely versatile.
Thanks for taking the time to reply. The only problem I have now is running two advanced filters within the same workbook. I'll explain the scenario.
I have 2 Userforms in my workbook. Userform1 is your PhoneBook with the date in Sheet1. Userform2 is my own creating but using your method, with the data in Sheet2.
The problem is getting Userform1 and Userform2 to work at the same time. The Criteria and Extract range does not seem to switch between Sheet1 and Sheet2. Is there a way to point the Criteria and Extract to the right sheet depending on which Userform is Initialized? The reason why I want to do this method, is that if you click on Look Up Code and the search criteria box is blank, it still brings out the entire list of data — this will be handy for my users if they do not know what to search for.
In your other projects you have used a different method to look up data Staff Database in this userform if the search criteria box is empty it does not return any data in the listbox.
This will allow you to interact with other objects including user forms. This is not something I would generally do. It is better to call the second user form from the first and then hide or unload the first user form and then add the same options to the second user form. You can explicitly reference the criteria, copyto range and extract range in your advanced filters.
Here is an example below here I have used the sheet codename not the sheet name. Range "C8". BK8" , CopyToRange: Range "BM8: BP8" , Unique: By the use of operators such as the wildcard you can make your search much more explicit and give the user the option to choose whether they want to search containing or perform an exact search. Referencing the criteria and extract to the sheet name and not code name did the trick!
I dont know why I didnt think of this earlier. Learning all the time. I lOVE! Hi Gicel, There is a project that shows how to show pictures in a Userform. It will help you to understand the concepts involved. Here is the link. And I'm having error because of this line. Hi Christian, Drng is for column B So the last line should now be the first line of code as this would be the first value entered. Hi Trevor, I appreciate your website, extremely good work. I have an issue with the form; just wanted to know if you could help.
When entering data in fields, after I hit the TAB key it doesn't follow through horizontally to the next field instead does a diagonal jump to a field below. Hi Thomas, You set set the controls tab order by right clicking a selected userform and choosing the feature Tab Order.
They can also be set from the VBA ribbon from the View tab. Bet wishes Trev. If Me. Hi Trevor, Thanks for responding to my question so quickly; I'm currently working on another one of your tutorial so ill try than code later.
I am having an issue when completing the tasks in Video 4. I enter the code and click add contact but it does not show up in the database and I get Error "Application defined or Object defined error" and it says my error is in the line: End x1Down.
Hi Kaitlin, If you are using the downloadable template the suggestion below will not apply. Check that the name of the control in the userform is named txtSurname.
It must be spelled exactly as in the code. Otherwise you could try below. Add some data to the first line of the data base in the worksheet and then try the code. Trevor, I tried your suggestion of adding some data to the first line and then trying the code but it still says I have an error and need to debug the same line of code.
Do you know of any other ways to fix this? Hi Katlin, if you are using the template provided and have not modified it I would be happy to have a look and identify the cause of the error. I can be contacted from the contact page of the website. Please supply the information requested. Best wishes Trev. Hello Trevor, Your tutorials are excellent. I have learned so much about excel and VBA working through the phone book project.
Thank you so much for all your hard work on this website.
After working through the project as suggested everything works perfectly but I seemed to have run into a problem with my modified project with editing, deleting and the double click command. I would be very grateful if you could just point me in the right direction to solving this error message that appears upon double-clicking the contact: Invalid argument. Never mind Trevor. I solved the problem. Neglected to correct column count in the outdata named range. I've completed the tour and have also downloaded the full code.
My modifications worked fine to begin with, but I ran into problems when adding extra columns. I want to ad 3 more columns and did the setup in the PhoneList form, adding the ColumnCount to 9. Added the ColumnWidths for the extra columns.
Ajusted the code in various places since the ID column had moved and so on. But it did not work. I can add a new contact including all the new fields but only 7 will show. I can see in the "database" that they get to the right places.
I can even see that the listbox makes room for them all. When I doubleclick on a contact I get a "Could not get the Column property. Invalid argument" error from Excell.
But it fills the fiels regardeless. When I try to edit I get the "Fields not complete" error. I went back to your code the full template and added just 1 column. Did all of the above modifications but still same result. Hi Simon, If you are adding columns you will also need to add controls to the userform and add them to the code as will.
The reason the list box is still showing 7 columns is because of the dynamic range "outdata" The listbox is populated by a dynamic named range. You would also need to adjust the listbox properties as show in the Videos.
Some videos you may like
Thank you so much. Seems I had the same problem as Ariel. She found it herself though. I had found all other places except the one in the Name Manager. Just in case anyone is interested, this is the code I used to print my userform. It's quite large and this code makes the form print out in landscape mode so that if fits nicely on a file folder.
I've checked that page out and as you said the code is quite large. Hi Simon, I have added some simple code to do this in the appendix of the tutorial. Looks much less complicated than the one I found! Will this code print the userform as it appears or just the data from the spreadsheet? I used the revised code at the end of the post for the print button and added a declaration sub at the top of the vba code.
Hi Deleece, Thanks fr the feedback. One of the properties for a textbox is PasswordChar you must have an x in that property. Remove the X from this property to show the numbers.
I've had a similar problem that was related to how the cells are formated in the database. This also caused a sorting problem for me as numbers were saved as text and sometimes as numbers. I went over the database and changed properties for the different columns. At first I even had numbers showing op in scientific format 8.
If you look at the databse and see numbers wrong — then that could be the problem. First of all I'd like to thank you for sharing this valuable tutorial with the world and express my personal gratitude to you.
So I started almost from scratch as I built up the GUI, adding ease of use and intuitiveness to it, did some data validation and etc.
And adding your code was the thing that brought life to all this, it was the bloodstream of it all. I was so excited and I wanted to enhance the functionality by adding a second criteria select field which applies a new filter to the pieces of information that the first one shows in the list box. I am afraid that this is tougher than me and I wouldn't be able to do it on my own. Your advice will be appreciated on solving this matter.
Hi Zlatin, Thank you for the feedback. To add more criteria to searching you would need to understand how an advanced filter works. I have added a link below to a simple advanced filter tutorial. You would need to add an extra criteria and then adjust the code to suit as well is out an extra control to your user form. I do not do modifications to applications is my schedule does not permit it.
It all starts to make sense to me now. You really have a way of explaining things methodically and understandably. I wish you all the best! Fantastic Trevor Was working on a small project and searched the web for answers to what I was trying to achieve.
Decided to watch all your videos on the telephone book, and all the solutions I was searching for are in this tutorial. Although they need to be adapted to my needs, but they are exactly the answers I needed. Can only say a big thanks once again absolutly fantastic site,thanks for all your work you put into your tutorials and educating people like myself.
Hi Trevor, good job on the project, my question is where I need to include "include operators to get a unique value. Hi Fredy, Searching by phone number may be an issue if there are spaces in the number. So basically what I'm saying is that your number formatting would have a bearing on the effectiveness of the search.
Simple phonebook macro
Best wishes Trevor. Hi Trevor Thank you so much Is it possible to use the Arabic language When a name is written in Arabic and I want to search by first name and typing the first letter of the name and the pressure on "cmdContact" receive an error message Is it possible to search in Arabic, put the first letter only, without the need to write the full name thank you.
Hi khaled, I do not know the answer to your question. You would need to test it in the language of office that you are using..
Thanks for sharing this valuable tutorial with us. I'm new to VBA, but have have followed your tutorials, which are absolutely informative and easy to follow, and have now completed a rough form. I've adjusted the tab order as per one of the above queries — thanks for that. I'd be very grateful you could outline the code to add two or three more boxes on the user form. Hi Roy, Thank if your communication. Each control that you add to a user form has a set of properties you can access these properties by right clicking the control in the VBA editor and choosing Properties.
You will you will notice a property called Font that will allow you to change the font style and also the size and characteristics of the font. To add two more controls you will notice on the phonelist sheet that the columns G and H are empty. You could use these two columns to match the two new controls that you add to the user form. Because the user form runs an advanced filter to extract the data. The two new column headers would also need to be added to the copy to range of the advanced filter columns U and V.
You would then need to adjust the parameters of the advanced filter to accommodate the two new columns. Range " N8: T8 " Instead of T8 the reference would now be V8. There is a dynamic named range that populates the listbox in the user form and it would need to be expanded to accommodate these two new columns.
In the properties of the listbox in the user form he would need to add the column count to now accept the new columns If you wish to view them.
There may be a few other things that are necessary but this will get you started and give you some idea of what is happening. Please accept my very best wishes Trevor. Hi Trevor, many, many thanks for your help and assistance with my queries.
I've altered the font and I'll now have a go at adding the additional columns.
Your explanation is so clear and helpful. Although I've only read, and worked through, a few chapters I've found this to be an excellent resource for a beginner like myself.
I would urged anyone who is thinking of learning VBA to download this valuable resource before embarking on any other learning aid. Hi Roy, Thank you for the feedback about the book. I am very glad that you have found it useful and instructive.
Thank you for supporting the website by downloading VBA for Beginners. Please accept my best wishes Trevor. Hi Trevor, first i want to thank you for creating these amazing posts, the way you explain everything makes it look so easy. Much appreciated. Hi Exionpt, I think I understand what you want to do. First of all the listbox that shows the results of the search is populated by a named range that picks up the results of a Advanced Filter.
In this tutorial the advanced filter has one criteria. Advanced filters are truly awesome and you can run multiple criteria and you can add operators to those criteria as to pretty much filter anything that you want. It is possible to have what is called cascading data in a combo box.
So when you select for instance a category then the items will appear in the next combo box or text box that are only the items for category.Jan 6, Facing a tech roadblock? If this occurs type the named range in or remove the double quotes after you paste. Offset 0, 2. Search Advanced search….
January 14, at 7: Here are the steps:. I have done some work in the past which is similar to projects that you have covered.