Automate Filling Out Excel Form

[Operater]

Hello,

I'm new to VBA programming and to this forum. I've been tasked with a few projects for work that require VBA and excel so I suspect I'll be spending more time here

I would like to automate filling out a form within a multi-sheet workbook. I have no control over the formatting of the form. I have the information that will go into the form contained in a database though.

I have compiled a table (saved in a separate excel worksheet called DATA) with the following headers to assist in doing this:
TABNAME - Names of worksheets
FIELDNAME - Names of the fields within the report, e.g., First Name, Last Name, Date of Birth, etc.
POSITION - Relative position of cell containing the Value corresponding to the field. For the form I'm filling out, the value is always located to the right or below the cell containing FIELDNAME.
VALUE - The actual values. These are all known in advance.

I would like to do the following for each row of the above table:
Find the cell location within TABNAME that contains the string FIELDNAME, then input VALUE into the adjacent cell determined by POSITION.

Any guidance on where to start would be appreciated!

[aditi7]

you mean like a userform? U need to make a userform? Or is it already made and you need to tailor it?

[Operater]

Hi aditi7,

The form is already made, and I have to work with it as is. It has been provided to me by a vendor for me to submit information. I'd like to automate inputting the data, as I need to fill these forms out frequently. The table, described above, to assist in the process is automatically generated from an access database I have created. So I have solved half the battle.

I have experience creating access reports, but the form is too complex for me to reproduce as an access report with the information filled out. Besides, I would not want to have to change the report every time the vendor decides to change the form.

So I have the data, the field names they are associated with, and the relative position of the cell containing the values with respect to the cell containing the field name (as described in the original post). I just need to figure out how to locate the position of the field name (rather than hard coding this into the macro, since the position may change with a revised form), then input the corresponding value in the adjacent cell.

Thanks!

[Operater]

Here is a (basic) example form to help clarify:

Example Form.png

I need to find the locations (col, row) of the field names, which can exist on different worksheets within the workbook.
I know in advance where to input the information, e.g., the first name is inputted 1 cell to the right of FIRST NAME, and a past job is inputted 1 cells below EMPLOYMENT HISTORY. A second job would be inputted 2 cells below EMPLOYMENT HISTORY, etc.

[aditi7]

you want a data entry userform which you can use to input into the specific rows and columns?

Since the columns and rows do not change at each entry , it will be easier.
I will send you a sample let me know if that's what you looking for.

[Operater]

My understanding is that with the data entry userform, I still have to manually enter the data... My goal is to automate the input as well.

I'll take a look at your sample userform to see if it does the trick, or at least helps me on the way. Can you attach it to a post? Thanks!

[Operater]

Partial Solution

I made this much more difficult than it had to be:

If I append my list of values that need to be inputted throughout the workbook to the final worksheet, say worksheet 8, looking like below:
Value1
Value2
....
ValueN

Then I can go through the workbook and enter =sheet8!$A$1 (or 2,3,...,N) in the appropriate places. The only manual part of the process, is exporting the list from the database (which is easy) and then appending a worksheet at the end of the workbook where I paste the list. I can then save the workbook with a different name, keeping the original as a template to reuse.

This alone would provide significant improvement on efficiency. However, if there is a method to automate the manual portion I'm all ears.

I'll go ahead an mark this post as complete in a couple of days.

Thanks!