Code Needed and Userform assistance

Personally I think you'd be better off using MS Access for what your wanting and not excel!

Thanks for that, however, unfortunately we dont have access at work, for some unknown reason....

So it has to be excel unfortunately

Your post does not comply with Rule 1 of our Forum Rules. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

thanks mobidv. Much appreciated.

Sorry about the post title, i noticed that in the rules but thought it meant like i had done it, I shall ammend now.

Thanks

Hi,

It's not code, but have you tried experimenting with the standard Data Form functionality that's in Excel. This allows you to add new records to a pre-existing list of data.

If you really want to use a User Form, add a combobox or a text box to capture the input, then add some code to the AfterUpdate event. So for instance if your table of data has a range name "Data" and the combo/text box is meant to add a new entry to the first column in the Data table, use something like

Please Login or Register  to view this content.

For other comboboxes, just change the second element of the .Cells(1,1) code, to .Cells(1,2) or .Cells(1,3) etc.

To detect duplicate names, one way would be to also have the combobox update a defined test cell, say above the list. Then adjacent to this cell use a COUNTIF() function to count the number of occurrences there are of the test cell in the relevant column of the data table. Then wrap the COUNTIF(() in and IF() function so that you have:

Please Login or Register  to view this content.

Hope this gives you some ideas

Rgds

Very good. This is basically what i am after... In fact it is exactley what i am after, Ideally the screen would load up as soon as the sheet is opened but i dont know if this is possible?

If someone could point me in the right direction on how to add something such as an alert for a name if typed into the box this would be absolutley fantastic.

Anyone?

Regards
Mark

Originally Posted by cruiser102

If someone could point me in the right direction on how to add something such as an alert for a name if typed into the box this would be absolutley fantastic.

Anyone?

Regards
Mark

What sort of alert are you looking for? The current form will bring up an alert if a duplicate record exists (It checks first name surname and address)

Ah, Sorry, i didnt check this.

I have just tried it and it is great, however if i put two similar names in such as Joe Bloggs, although they may be a different date of birth it still picks it up as a duplicate record. It is often that people with the same name may be entered, but they will have a different date of birth.

Kind regards
Mark

Originally Posted by cruiser102

Ah, Sorry, i didnt check this.

I have just tried it and it is great, however if i put two similar names in such as Joe Bloggs, although they may be a different date of birth it still picks it up as a duplicate record. It is often that people with the same name may be entered, but they will have a different date of birth.

Kind regards
Mark

Mark,

Aslong as the two people both have a different address it should let you enter more than one with the same name.

This document is great, I have ammended the userform slightly and wish to add another column to the document Team A or B as seen on the new userform.

Couple more issues now though

1. I cant get the options to be in the drop down menus such as yes/no and a/b

2. When on the userform, if i press TAB to jump to a different box it goes from top to bottom box then back up, all in the wrong order.

3. On the excel sheet with the button to open the userform, is there any way i can add a search tool on this screen where people can input a surname and date of birth to pull up the relevant person...?

Thanks for all your help so far.