Using Excel data entry forms

Question

Is there a way, using the Excel data entry form box, to specify which worksheet you want the entry to go into?

Example, in an inventory spreadsheet I have a sheet for "Tools" and a sheet for "Safety Equipment", both sheets have the same header information. I would like to find a way to use the data entry form to enter data from an invoice and specify which sheet I want the information on.

Any suggestions? Using Excel 2007, on XP @work, but Vista @home.

Answer 1

Hello, Nightowl

Please remind me where to find the Excel Data entry form box...  It is a very badly documented gadget and I can't for the life of me remember where they have hidden it!  I am also on Excel 2007 on XP

It would be quite simple to build a data entry box with sheet selection, but I guess you are hoping not to do this.

Regards

geoff

Answer 2

This is very interesting.... I am currently using an invoice data entry form with up to 7 items per invoice.  And I post all items to one sheet because I want to keep the invoice in tact.  I then loop through the invoice sheet to split the items out to the different category sheets.

what you want to do may come in handy and I ran a quick test.

invoice input form

ITEM DESC.          CATEGORY

hammer               1

hard hat              2

etc                     etc

with a post button            only to demo the indexing option

Dim x As Integer

x = category.Value

Worksheets(x).Select

Range("a1").Value = item.Value

By using the category as sheet index you can post directly to the sheet you want.  this goes to sheet1 and sheet 2

This is bare bones example that I used to test it,  but should point you in the right direction.

I will be watching for other responses.   There is probably a better way    John

Answer 3

For further help on this issue you may also post your query in the link given below:

http://social.technet.microsoft.com/Forums/en/excel/threads

Answer 4

No, the invoice would be where the data was coming from.

Answer 5

Is ‘Invoice’ a separate sheet or a separate excel file?

When you enter the data in the data entry form the title of the data entry form contains the name of the worksheet on which the data will be entered.