formula or macro

[Guest]

I’m trying to create an Excel DB in which a form is used to enter data into a
table. The built-in form is not sufficient but is very close.
i need a formula/macro to enter info gathered on form; placed into a table?
For example: I want to collect Name, Address, Phone # on a form looking
worksheet and have that info put into another worksheet in columns.

(e-mail address removed)

 

[Don Lloyd]

Hi,

Pointer.

Set the form up on its own sheet, say ("Form") and the database on a
separate sheet, say ("Data")
Assume the Form Info is held in C5 to C10, the database columns are C to G
or, in R1C1 notation, columns 3 to ,. and you wish to put your form data to
Row 10 in the database.

Sub FormToData()
Sheets("Form").Range("C5:C10").Copy
Sheets("Data").Cells(10, 3).").PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
End Sub

To retrieve the data from the database

Sub DataToForm
Sheets("Data").Range(Cells(10, 3), Cells(10, 7)).Copy
Sheets("Form").Range("C5").PasteSpecial _
Paste:=xlPasteValues, Transpose:=True
End Sub

It is advisable to keep track of the database row number as a variable, say
Rw and use this in place of the 10 in the above code. e.g.
Sheets("Data").Range(Cells(Rw, 3), Cells(1Rw, 7)).Copy . . . etc.

It is feasible to automatically up-date the database as data is entered on
the form but it is a little more complex.

Hope this helps.

regards,
Don

 

[Guest]

thanks Don. i understand your solution and it works very well. how much
more "complex" to make it automatic?
Do you know how to (using the old programming language) 'pop' a number in a
loop?

 

[Don Lloyd]

Hi,

1. In "Form View" you need to know the row number of the current record. (
DataRw )
You could store this in a cell on the Form sheet, say "A1"

2. Determine the relationship between the "Form" row numbers and the record
column numbers.( DataCol )
e.g. If Surname is row 10 on the Form and column 5 in the Data sheet then
the relationship is -5
i.e. DataCol = Form Row - 5

3. Use the WorkSheet_Change routine to transfer changes on the Form to the
Data sheet.

DataRw=cells(1, 1)
DataCol = Target.Row - 5
Sheets("Data").Cells(DataRw,DataCol) = Target.Value

The above outlines the basics which I hope you can follow.
In practice you need to, for example, limit the transfers to the column
containing data on the Form and ensure that you always have the correct
record row number, especially if you wish to use NEXT, PREVIOUS and NEW
buttons on the Form Sheet.

Regards,
Don

 

[Guest]

thx Don
it will take me a while to decipher this latest. i know what i'm doing i
just havent done it in a while
i created a list box of the names, which gives me the row on the data sheet,
and i take the data from the form to a temp sheet, where i rearrange it to
suit my needs, then i use the basis of your macro to move it to the data
sheet and the row designated by the number of the list box.
i'm getting closer to my goal, thank you very much.