Create a Custom Data Entry Form

I am having trouble creating a custom data entry form in excel. What steps would I need to take..

Attached is a example of the data, the Headers are in bold, the highlighted columns are to be drop boxes.

Thanks In Advance.

JG

Originally Posted by Nolesphan30

I am having trouble creating a custom data entry form in excel. What steps would I need to take..

Attached is a example of the data, the Headers are in bold, the highlighted columns are to be drop boxes.

Thanks In Advance.

JG

Hi,

I'm not clear what additional functionality you're looking for. You already have Data Validation lists with associated drop downs attached to your highlighted columns.

Can you explain a bit further? Why complicate what you've got - or are you looking for a gee-whiz VBA form to capture the same sort of stuff that you're Data validation already does?

Rgds

What I'm looking to do is have a form open automatically when someone opens the book. Is this something that can be done?

Basically I can go with what I have, just wanted to see if that was an option.

Originally Posted by Nolesphan30

What I'm looking to do is have a form open automatically when someone opens the book. Is this something that can be done?

Basically I can go with what I have, just wanted to see if that was an option.

Ah, OK.

The simple answer is Yes.

How familiar are you with VBA? In outline you need to:

1. Design a form with 5 ListBoxes to hold all the allowable entries for your 5 fields. Use the RowSource property of each List box to point to the allowed values in columns K:O, and a couple of 'Accept' or 'Close' buttons.

2. Have the form show by putting "MyFantasticnewForm.Show" in the Workbook Open event

3. In the Click event of the 'Accept' button, wrote some code to update the contents of the 5 list boxes to the database.

4. In the click event of the 'Close' button enter the code 'Unload.Me', and set the 'Cancel' property of this button to TRUE.

That's about it. If you need help with writing the specific code, please post back.

HTH

Im starting to get more into VBA. But I'm just a beginner... I have trouble with the actual code..

Originally Posted by Nolesphan30

Im starting to get more into VBA. But I'm just a beginner... I have trouble with the actual code..

I'm attaching one solution

Press the CTRL m key or click the icon to add data. I've only shown 4 boxes since I guess you only want to capture the person's name rather than both the name and ID. Otherwise you could get someone who picks one name and a different ID. I've arranged this macro so that it looks up the ID once the name is entered.

As you add data, you won't see the new records until you close the menu.

Hope this gives you some ideas

Regards

This is great!

One question, how can I add the fields to the form to add the date and score?

I really appreciate your help!

Originally Posted by Nolesphan30

This is great!

One question, how can I add the fields to the form to add the date and score?

I really appreciate your help!

Hi,

How familiar are you with VBA and the forms?

If you want to add the date and score at the same time as the other stuff, it's fairly simple. If you want to add them independently, and at another time, then the macro will need to be changed since it will need to be told which record you're trying to update.

If you're capturing a date it would be best to build in a pop up calendar from which the user picks a date.

What about the score? Are there any limits or rules to this, i.e. must be an integer between 1 and 100, or is it unlimited. Is it always numeric?

If you let me know I'll add them and forward a revised version. I'm actually away until tomorrow night (UK time), so probably won't be able to do anything until the weekend.

Or if you want to have a bash, you will need to add a Combobox for the score, (as opposed to the Listboxes I've used for the other stuff since their entries are pre-determined), and a Calendar object.

Then in the Module1, in the Procedure 'Sub AddToDatabase', you'll need to add a couple of extra lines of code to pick up the values of the new Combobox and the calendar control, and update these in the database.

Regards

I just want to add the date and score at the same time as the other stuff. (Same Form)

A pop up calendar would be great, would that be something fairly simple to do on the existing form?

The score is always numeric and must be an integer between 1-100.

Another question I have is if I needed to change or add to the data thats on the combo list, how would I do that?

Originally Posted by Nolesphan30

I just want to add the date and score at the same time as the other stuff. (Same Form)

A pop up calendar would be great, would that be something fairly simple to do on the existing form?

The score is always numeric and must be an integer between 1-100.

Another question I have is if I needed to change or add to the data thats on the combo list, how would I do that?

Hi,

I'm attaching the workbook with the changes you mentioned.

Hopefully it should be intuitive. The menu brings up 4 boxes for adding to the database, one scroll bar for setting the score and a calendar. The user Id is a function of the user name and doesn't need a box. If you don't see the calendar, it may not be loaded as a VBA object. Go to the VBE with ALT-F11, choose Tools-->References and look for a calendar control object and tick it.

Now back to Excel with ALT-F11 again

On the right are three option buttons which handle additions and deletions to the various names and ids.

Option button 1 is the default and allows you to add to the database.

Option button 2 pops up another menu where you can add new items to the pick list tables. I've arranged it so that it won't allow you to enter a name without a user ID or vice-versa.

Option button 3 allows you to delete names from the tables.

Regards,

Hex this is great, I really appreciate your help with this.

I have one more question for you. Not related to the form. On my Agent Summary page, I have it where I can select the user to see their results. How can I also add on there if I wanted to select between a date criteria? For ex. Like between 01/01/08 and 01/31/2008? Or First Qtr, second Qtr...

Originally Posted by Nolesphan30

Hex this is great, I really appreciate your help with this.

I have one more question for you. Not related to the form. On my Agent Summary page, I have it where I can select the user to see their results. How can I also add on there if I wanted to select between a date criteria? For ex. Like between 01/01/08 and 01/31/2008? Or First Qtr, second Qtr...

Hi,

I'd be inclined to add a new column D and make C25 the start date for a date range selection, and D25 the end date for selection. You could also validate the entries in these cells by choosing the Date option from the validate list rather than the List option. Or alternatively you could have a pop up calendar much like you have for the form, and have the dates entered by clicking the calandar.

Then you will need to build the two dates into your four columns of formulae. That said those formulae are getting pretty complex already, and personally I wouldn't choose to do it that way. You are limiting yourself to rows 21 to 96 (in this admittedly shortened example), but more importantly making the workbook bigger because of the formulae.

It's more usual in a case like this to use Data Filter Advanced. So that you name the database range, and the output range, in your case E25:K25 (assuming you've already added the extra column), plus the criteria range B24:D25. You could easily add a macro to run this if you didn't want the hassle of manually running the Data Filter. It's just a single line of code.

I've just noticed that on the workbook I returned to you I had deleted the validation lists that you used previously to select names, and that you need a duplicate copy of the names on my Variables sheet put on the Agent sheet. It would be sensible to have this refreshed every time a new user was added via the form. Again it's just an extra line of code.

Hope this helps a bit...

Regards