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, Arabic, French|
|Genre:||Politics & Laws|
|ePub File Size:||26.51 MB|
|PDF File Size:||13.70 MB|
|Distribution:||Free* [*Sign up for free]|
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.
That tells Microsoft Excel that that is the area you wish to now edit. The List range The list range must include the heading along with all of the data. There should not be any blank rows in your data. And it is best to surround the data set with blank columns and rows so that Microsoft Excel can recognise this as a data list.
The Criteria range. The criteria range should include both the header and that the criteria with no spaces. Here are the 3 steps for this project's advanced filter Record the advanced filter for the Excel Phone Book Now lets run the advanced filter and record it as we go. Click on the Developer tab. Now click Record Macro and the macro dialog box will appear. Give the macro a name one word or multiple words joined with underscores make sure theStore macro in: is This Workbook.
Simple phonebook macro
Push the cap lock on your keyboard and in Shortcut Key: type a letter choose a letter ,let's say A. This makes testing easy. Here is your challenge. I like to try to provide projects that can be used across platforms from to if possible.
I like to try to provide projects that can be used across version from to if possible. Thanks very much for watching the tutorials. I appreciate your feedback very much Kind regards and best wishes Trevor Easton. I have followed the tutorials through and its working perfectly. I am a beginner in vba and this has been very helpful. Greetings John, The information on the listbox is taken from a named range on your worksheet. It is quite easy to add a command button on the user form to print that range of data.
I think from memory it is a dynamic named range called "output"or something similar. If you like I can send you a little demonstration workbook to show how to do this. I hope this answers your question. If I missed the point please let me know. Kind regards and best wishes Trevor Easton. Hi Jenseeraq, I will post an appendix to the article to show how to do this shortly.
Wow what a fantastic tutorial. I used to do quite a lot of work on excel and got board after a few years, however after having watched some of your tutorials you have inspired me to go back to it. Where I thought I had got as far as I could you have shown me new ways to make my work even more presentable. I have recently re-written a program for my company where uses were lost and didn't understand how to use it.
I have adapted what you have taught us in the "Simply the best phone book" and now they will be distributing the program throughout the business as a productivity time saving tool. Thank you again for your time in sharing your knowledge.
I will try to pass this knowledge on to other enthusiasts alike. Hi Cliff, Thank you very much for your positive feedback. I actually based this program on an application I wrote for a hospital and I work in. It is been in use now for many years and works fine. Thanks once again kind regards and best wishes Trevor Easton. Wonderful piece of work, Trevor!! A question: I can search surname or Firstname just by typing a single letter, and name containing that letter will churn out perfectly.
However for Phone and Mobile, if i will to key in a single or few numbers but no full numbers , it will never give me the result. How to make it work similar to the Surname or Firstname search? Greetings, The reason is the the phone numbers are numbers the rest are text. For numbers you need to include operators to get a unique value. Try also removing phone number prefixes and spaces. Hello Mr Trevor, Thank you for this fantastic work.
I have a question: I dont's understand Me. Excuse my bad english because I am algerian. Greetings, It is at the bottom of the userform the background color is the same as the rest of the userform. I have sent an image of it to you. There is no need for the user to see this control. High Dave, There are a number of ways to achieve this. This will allow the VBA editor to float. Another and more effective way is on the View tab choose the command New Window in that will open your application in 2 windows.
All you then need to do is open your VBA editor in the second window. This is the best and easiest option. That do not forget that you are actually working now on to Windows and you will need to close one before you save your file. Sorry for the delay in responding to your question. Best wishes and kind regards Trevor Easton. Given I am new to vba excel do you recommend starting from scratch rather than modifying the completed project?
I know I could do with lots of practice however I am tempted so save a bit of time. Hi David, I would be a lot quicker if you have the finished product. It is highly recommended that you go through the tutorials and check the information on the file to see how the project is structured particularity if you want to modify it. Yes the find is used by looking up the auto-incremented ID number but the information is edited on the userform and then sent back to the database.
I do not use the replace function in the app. Hi Trev, what a great site, the best excel site I've come across.
Download excel phone list template
The templates are superb and the videos very professional. Your commentary is so informative and delivered in such a calm and relaxed manner, it's a real pleasure. Thank you. I was mainly interested in the Staff Leave Planner template, but the other templates are just as interesting — great job! Thanks Mike, I appreciate the feedback. The projects are a lot of fun but the website and videos are a huge time investment.
Your supportive comments and encouragement mean a lot. I want to thank you for your help you have given me on the phone book project, I have now added the extra columns and modified in the way that I needed. I find your site very helpful with lots of video tutorials and cant wait for your next projects to be added do you know what they will be? Do you have project for recruitment management i. You mentioned in the phone book video 5 that there is some code on your site for protecting the book, where about is that.
Hi Mike, I will have a look at adding a short section to the end of the project with code and instructions for automatic protection of the project, I will send you an email when this is done. There are 2 projects that HR departments have used. Best Wishes Trev.
Hi Trevor i would be interested on the "automatic protection" for this project can you also email me? Thank you very much for the great exercise.. For some or other unknown reason I am not able to download the Template. I humbly ask if at all it is possible that you e-mail me the template please.
Thank you for this great learning program and site! I have already bought some of your programs, but better to learn with your lovely guidance and templates, but time consuming. Today I went through on the "Simpple the best Phone Book" training. I have done "My Finances" as well.
The reason to contact you is the following. Hard to find any. I developed a quite complicated test resoult evaluation program with heavy calculations in excell without any developed excell, VBA and Macro knowledges. However I turned this excell intoo programm with DoneEx compiler.
I sold some programmes as my solution very rare, but I am not satisfied with it. A few program sold oversees to USA and Australia as well. But looking at your perfect solutions, I am not satisfied at all with my "programm"…so if I could pick up enough VBA knowledge I would like to redesign my program. Now I try to learn from your programs how to develop a better solution, with dinamic name ranges, advanced filters and Customer Forms.
My test evaluation programme's market is very limited, so very rare solution available, inpossible to finance professional programmers, the big players avoid this field.
The problem is that most of your solutions does not fit to my technical demand, now I know how export and import Cutomer Forms and Moduls. But need to rewrite and reconstruct all things and long way ahead when I can write such solutions than you. Dou you have any advise how can I speed up my learning with focus on to my technical solution.
Dou you see any possibility to develop a more or less uniform technical example. Hi Istvan, The best way that I know to learn is through project development. This certainly has accelerated my learning curve. The projects on Online PC Learning are presented a basic as possible to accommodate easy learning. Business applications would be far more complicated and and of course expensive to develop as you need to factor in specific customer needs and end user training.
They are not suited to a progressive learning curve. The acticle on Developing Bullet Proof applications may be of help. I have worked on the PhoneBook exercise and tailor designed it to my specific needs. Thank you very much this indeed is a masterpiece of a document.
I wish do add a "print Button" on my file, how would I go about doing this if at all it is possible? Hi Mustang, I am glad the project if of benefit. Assuming you have used the code on the website which contains some basic error handling then your VBA editor error options is not set correctly.
Make sure that your Excel application has the right options checked to enable you to deal with errors properly.
To access this dialogue box in the VBA editor choose Options are from the Tools menu and then click on the General tab. Notice the section here for Error Trapping. 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. 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. 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. 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.
Clean up Incorrectly Formatted Phone Numbers using Microsoft Excel -
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. 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.
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.
Download excel phone list template
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.
This is demonstrated in the tutorial below. Hi Trevor, I tried creating 2 of this forms, and each form has its own dump, Worksheet 1,2 but im getting error, seems like when I recorded a new macro the 1st macro will stop working, even I change all the names in name manager and macro name.
Hi Ryan, I am not really sure what you are referring to. Are you working to develop this training project or a modification of it? Yes I am developing this project, but this time I created 2 Forms exactly the same and of course it will dump to 2 different sheets. When everything is working with the first form edit, delete, add, everything , and start with the 2nd form. After the recording of 2nd macro and try if the 1st Form is still running I'm getting this error: Is it because I can only record 1 advance filter macro?
The VBA Tutorials Blog with Ryan Wells
Hi Ryan, "the extract range has missing or invalid field name" probably means that the headers for you filter are incorrect. The Extract range and the Criteria range and the Copyto range need to have the same headers spelling. Greetings Trevor, i have been learning a lot from this phonebook, trying different things, exploring, etc, so thank you very much for that, i have another question, if i want to add another combobox to filter only the first colum Range "B8" and get results right away and then with the cboSelect combobox select any other of the remain colums Range "C8: H8" and in the text box type something to get the results, for example i have names including repeated ones and i want to see only the unique names on the first combobox one each for this i think i need another sort function if i select one i get a list of all those names including repeated ones and then on next box i want to find adresses or numbers in all the names not only uniques and then i type something and hit search how will this work before the transpose since it only transposes after we hit the search button, is the filtering and sorting done all at once?
Do you happen to have a tutorial for multiple comboboxes doing these functions? Hi Exionpt, The filtering in this project is done with an advanced filter. One of the features of an advance filter is to be able to filter unique values. Here is a link to a basic tutorial on how advanced filters work. You may find it beneficial to have a look through it before proceeding with modifying your application.
This is sameer from India. Can you also please help with the Export to excel command code in the user form? Hi Trevor, once more thank you for your great tips, i think i see it now, something like you explain in your finance calculator project, cascade combos and then use the advanced filter on multiple criteria and get the filtered data results from it, thats the path ill take. Please, I'd like to thank you for sharing this valuable tutorial with the world and express my personal gratitude to you.Cheers Mate.
The columns header may be different, some columns may be added, some deleted. I had to fix all " and ' since it pasted wrong simbols. You need to do the following: You will see all of the named ranges in the name manager. Filter contacts by all headings Filter by full or part references Data is auto sorted Add data is indexed so no duplicates Add new contacts from the form Delete contacts Edit all or part of the database.