Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Data from "Form" to Spreadsheet
Hi Everyone,
What is the best way to pass data from a "Form-like" sheet to Spreadsheet (columns and rows)? I created a fillable form in Excel by locking area that user are not supposed to access (mainly field labels, name, address, and so on), and there is a little calculation involve in this form. How do I pass the data that user already input in to a spreadsheet? My original idea was creating a Macro for Print then Clear the form for later use. But with 2nd consideration, it doesn't make any sense to have the user fill this in, print it and then, clear it out, without passing the data to a spreadsheet for compiling and record-keeping purpose. Does anyone know what is the best possible way to accomplish this? Thank you very much, Neon520 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Data from "Form" to Spreadsheet
hi
not sure of your layout on your "form" but something like this might work for you. assuming that the first cell to transfer on your from is A2 and you want it transfer to sheet2 cell a2....... Sheets("sheet2").Range("A65000").End(xlUp).Offset( 1, 0).Value = _ Sheets("sheet1").Range("A2").Value you would need a line like above for each cell you want to transfer. adjust the ranges to suit. then to clear the "form"....... range("A2,c2,e2,a3,c3, e3").clearcontents you would need to add each cell on the "form" that you want to clear. you would also need a way to trigger the code to run. perhaps a button on the sheet. good luck regards FSt1 "Neon520" wrote: Hi Everyone, What is the best way to pass data from a "Form-like" sheet to Spreadsheet (columns and rows)? I created a fillable form in Excel by locking area that user are not supposed to access (mainly field labels, name, address, and so on), and there is a little calculation involve in this form. How do I pass the data that user already input in to a spreadsheet? My original idea was creating a Macro for Print then Clear the form for later use. But with 2nd consideration, it doesn't make any sense to have the user fill this in, print it and then, clear it out, without passing the data to a spreadsheet for compiling and record-keeping purpose. Does anyone know what is the best possible way to accomplish this? Thank you very much, Neon520 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Data from "Form" to Spreadsheet
Hi FSt1,
Thank you for your rapid response. I didn't get to try your code yet, but I have a follow up question for you. What do I need to do if I want the records to be pending every subsequent times the "button" is pressed, so that the all the records will be accumulated every time, rather over-write the old ones. Hope I explain myself this time. Thanks again, Neon520 "FSt1" wrote: hi not sure of your layout on your "form" but something like this might work for you. assuming that the first cell to transfer on your from is A2 and you want it transfer to sheet2 cell a2....... Sheets("sheet2").Range("A65000").End(xlUp).Offset( 1, 0).Value = _ Sheets("sheet1").Range("A2").Value you would need a line like above for each cell you want to transfer. adjust the ranges to suit. then to clear the "form"....... range("A2,c2,e2,a3,c3, e3").clearcontents you would need to add each cell on the "form" that you want to clear. you would also need a way to trigger the code to run. perhaps a button on the sheet. good luck regards FSt1 "Neon520" wrote: Hi Everyone, What is the best way to pass data from a "Form-like" sheet to Spreadsheet (columns and rows)? I created a fillable form in Excel by locking area that user are not supposed to access (mainly field labels, name, address, and so on), and there is a little calculation involve in this form. How do I pass the data that user already input in to a spreadsheet? My original idea was creating a Macro for Print then Clear the form for later use. But with 2nd consideration, it doesn't make any sense to have the user fill this in, print it and then, clear it out, without passing the data to a spreadsheet for compiling and record-keeping purpose. Does anyone know what is the best possible way to accomplish this? Thank you very much, Neon520 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Data from "Form" to Spreadsheet
hi
the code i wrote does not overwrite. it appends to the next availabel blank row. Breakdown.... sheets("sheet2"). _ 'working sheet 2 range("A65000"). _ 'start at the bottom of the A column end(xlup). _ 'go up till you hit data offset(1,0). _ ' drop down 1 row ie find next blank row value ' this is the stuff you transfering regards FSt1 "Neon520" wrote: Hi FSt1, Thank you for your rapid response. I didn't get to try your code yet, but I have a follow up question for you. What do I need to do if I want the records to be pending every subsequent times the "button" is pressed, so that the all the records will be accumulated every time, rather over-write the old ones. Hope I explain myself this time. Thanks again, Neon520 "FSt1" wrote: hi not sure of your layout on your "form" but something like this might work for you. assuming that the first cell to transfer on your from is A2 and you want it transfer to sheet2 cell a2....... Sheets("sheet2").Range("A65000").End(xlUp).Offset( 1, 0).Value = _ Sheets("sheet1").Range("A2").Value you would need a line like above for each cell you want to transfer. adjust the ranges to suit. then to clear the "form"....... range("A2,c2,e2,a3,c3, e3").clearcontents you would need to add each cell on the "form" that you want to clear. you would also need a way to trigger the code to run. perhaps a button on the sheet. good luck regards FSt1 "Neon520" wrote: Hi Everyone, What is the best way to pass data from a "Form-like" sheet to Spreadsheet (columns and rows)? I created a fillable form in Excel by locking area that user are not supposed to access (mainly field labels, name, address, and so on), and there is a little calculation involve in this form. How do I pass the data that user already input in to a spreadsheet? My original idea was creating a Macro for Print then Clear the form for later use. But with 2nd consideration, it doesn't make any sense to have the user fill this in, print it and then, clear it out, without passing the data to a spreadsheet for compiling and record-keeping purpose. Does anyone know what is the best possible way to accomplish this? Thank you very much, Neon520 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Passing Data from "Form" to Spreadsheet
Hi FSt1,
Thank you for your detailed explanation. One more quick question if you don't mind. Part of the data that I pass over also contain Credit Card Information. And I don't want to transfer the CC # since it is a privacy concern for everybody. I only want to transfer the last 4 digit of the CC# and put #'s (12 of them) in front of the 4 digits. Do you know how to manipulate this? Thank you very much, Neon520 "FSt1" wrote: hi the code i wrote does not overwrite. it appends to the next availabel blank row. Breakdown.... sheets("sheet2"). _ 'working sheet 2 range("A65000"). _ 'start at the bottom of the A column end(xlup). _ 'go up till you hit data offset(1,0). _ ' drop down 1 row ie find next blank row value ' this is the stuff you transfering regards FSt1 "Neon520" wrote: Hi FSt1, Thank you for your rapid response. I didn't get to try your code yet, but I have a follow up question for you. What do I need to do if I want the records to be pending every subsequent times the "button" is pressed, so that the all the records will be accumulated every time, rather over-write the old ones. Hope I explain myself this time. Thanks again, Neon520 "FSt1" wrote: hi not sure of your layout on your "form" but something like this might work for you. assuming that the first cell to transfer on your from is A2 and you want it transfer to sheet2 cell a2....... Sheets("sheet2").Range("A65000").End(xlUp).Offset( 1, 0).Value = _ Sheets("sheet1").Range("A2").Value you would need a line like above for each cell you want to transfer. adjust the ranges to suit. then to clear the "form"....... range("A2,c2,e2,a3,c3, e3").clearcontents you would need to add each cell on the "form" that you want to clear. you would also need a way to trigger the code to run. perhaps a button on the sheet. good luck regards FSt1 "Neon520" wrote: Hi Everyone, What is the best way to pass data from a "Form-like" sheet to Spreadsheet (columns and rows)? I created a fillable form in Excel by locking area that user are not supposed to access (mainly field labels, name, address, and so on), and there is a little calculation involve in this form. How do I pass the data that user already input in to a spreadsheet? My original idea was creating a Macro for Print then Clear the form for later use. But with 2nd consideration, it doesn't make any sense to have the user fill this in, print it and then, clear it out, without passing the data to a spreadsheet for compiling and record-keeping purpose. Does anyone know what is the best possible way to accomplish this? Thank you very much, Neon520 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting a spreadsheet into a "fillable" PDF form | Excel Discussion (Misc queries) | |||
Drop-down list on simple excel 2003 form using "form" from data me | Excel Discussion (Misc queries) | |||
any formula to convert numbers in word form, e.g. "2" as "Two"? | Excel Worksheet Functions | |||
Put "put user form" in spreadsheet | New Users to Excel | |||
Adding "New" "Insert" "Delete" into a workbook to change from data 1 to data 2 etc | Excel Programming |