Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
Im 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
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 "jyoung" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
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" wrote: 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 "jyoung" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
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 "Jyoung" wrote in message ... 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" wrote: 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 "jyoung" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
formula or macro
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. "Don Lloyd" wrote: 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 "Jyoung" wrote in message ... 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" wrote: 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 "jyoung" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula or Macro | Excel Discussion (Misc queries) | |||
formula in macro | Excel Worksheet Functions | |||
formula to a macro help PLEASE | Excel Discussion (Misc queries) | |||
Macro Formula | Excel Discussion (Misc queries) | |||
Macro/Formula Help? | Excel Discussion (Misc queries) |