Custom Data Entry Forms

**paxile2k** · 03-31-2010, 12:05 PM

Is there a way to view to code for the Data Entry form and edit it. I am trying to create a data entry form that will use drop downs and automatically put the current date in a column and a number(sequential) in another columns as well as the fields from the data entry form.

Any help would be appreciated

**Palmetto** · 03-31-2010, 12:45 PM

Is there a way to view to code for the Data Entry form and edit it.

I assume you are speaking of the built-in form. If so the answer is no.
You will have to create your own custom user form using VBA code.

**royUK** · 03-31-2010, 12:58 PM

See the DatabaseForm example here

http://excel-it.com/vba_examples.htm

**paxile2k** · 03-31-2010, 03:56 PM

Originally Posted by royUK

See the DatabaseForm example here

http://excel-it.com/vba_examples.htm

Thankyou both for your assistance, Thankyou for the examples they will certainly help out

It seems i still may have some questions on this one but i am still looking for some additional info, with the form i am also trying to have some other functions, i would like to have one of the fields be a drop down and example "Good,Bad,So So", i know data validation works like a champ in a situation like this, but not sure on the code for it, and if the add will do it correctly. Another situation is that i would like to have two other fields that will automatically poplutate the current date, with a second field that will insert a number sequentially, and example would be for the date of 03/30/10, will populate a 1, then next one will be a 2, and so on, then if going to 03/31/10, will start the count over from 1. This seems be a little more complex then i was thinking. Any advice would be appreciated

**royUK** · 04-01-2010, 01:28 AM

You can use a Combobox. Add one from the UserFom ToolBox

Load the choices like this

Please Login or Register  to view this content.

Use the selection like this

Please Login or Register  to view this content.

**paxile2k** · 04-01-2010, 02:45 PM

Thankyou for the suggestion, if was quite useful. After using some of the examples you provided, i am now running into an error just trying to test run the userform. I am getting a
"runtime error 1004: method 'Range' of object '_Worksheet' failed". It then pulls the properties under the userform name itself. Here is the code:

Please Login or Register  to view this content.

I am not sure what i am missing on this one

Anyhelp would be appreciated

**paxile2k** · 04-02-2010, 12:46 PM

Ok, I managed to correct the 1004 error by referencing the correct worksheet, apparently excel gets confused with sheets that are renamed. I am running the tests on the userform now but having some issues with the combobox(s) pulling the appropriate data from the sheet. I decided to go with a list option as apposed to a Array option as suggested by RoyUK just do the fact if i need to add options, it will pull from those instead of going in and updating the code each time. the problem is that is just does not pull it from the sheet. Example, sheet4 or "LookupList" has 4 single column list of data. (its not a actual created list just data) A1 has the name, A2-5 has name info. I am trying to have the associated combobox pull from that info of A2-5. I have checked the resources that royUK gave(excellent resource, can't thank you enough on that one

) but it seems that it is for 2007 Excel tho and i am using 2003. i am not sure how have it pull from the cells.

Any help would be appreciated.

**royUK** · 04-02-2010, 12:55 PM

What's for2007?

**paxile2k** · 04-02-2010, 12:59 PM

Originally Posted by royUK

What's for2007?

thankyou for your reply, the examples for creating combobox(s) in a userform, the screenshots appear to be from Office 2007. And i am using Office 2003.

**royUK** · 04-02-2010, 01:18 PM

Still works in 2003,try it

**paxile2k** · 04-02-2010, 01:37 PM

Originally Posted by royUK

Still works in 2003,try it

I have completed the steps but the combobox is coming up blank on the userform. I was able to set the named range. not exactly sure if the same as the example tho. Any further suggestions?

**royUK** · 04-02-2010, 01:59 PM

Not sure why it isn't working for you

**paxile2k** · 04-02-2010, 02:04 PM

Originally Posted by royUK

Not sure why it isn't working for you

me neither

, Could i have something wrong in the code by chance? In the examples you gave me, it gives info to set Dim cPart AsRange to then uses the cpart to set as "PartList" which cPart values set for value used in cboPart. Would it be helpful to you to see the workbook?

**royUK** · 04-02-2010, 02:10 PM

Attach the workbook

**paxile2k** · 04-02-2010, 02:15 PM

I have attached the workbook. Thanks again RoyUK

Found a solution to the issue,

Please Login or Register  to view this content.

this option seems to do the trick, i was hoping to have it pull from a list but this will do also., now i just need to see if i can have other boxes(combo/text) gray out based on value of another one. that way it prevents having to enter information that is not going to be needed. One question i did have, is if there is a way to have a counter option to enter a number based on the date, for example, if it is the 1st record for the date, then it would enter a 1, then second entry will be a 2, and so on.

Any help would be appreciated.