Okay, I have an Excel workbook that houses a form on the first sheet. What I'm trying to do is to automatically copy the data entered into the rows that are housing the inputted data into a separate sheet that will be used as a database. The database will maintain all of the various entries that have been made into the form for the various clients.
I was able to find an answer on Allexperts that basically delineates everything that I'm trying to do.
Here's the link:
I don't know how to create a shadow form, but I'm sure that's nothing a quick google search can't solve. I'd also like to create the SUBMIT button macro mentioned on the page.
The only other thing that I would like to do with my form that is not covered on the Allexperts page is to limit data entry to "specific" cells so that those entering data won't enter it into the wrong cells.
Any guidance or assistance in creating any of these items (shadow form, Submit button macro, dynamic row pointer) would be great.
I'll let you guys know what I can find.
One question, though:
Should the database include separate columns with headings for each of the rows within which data will be entered?
To create form go to visual basic editor (ALT + F11) >>Insert>> Userform
find how to fill controls (text boxes, labels, buttons) into a userform by searching in google
When userform is created and make a macro to show that userform
on clicking the submit button on the form,
find the lastrow in the database sheet (use google) , get all the field values to each cell in the last row
ok so after many weeks of fiddling about I finally developed a macro that you tag to a click button (from forms) right click and assign macro. Then paste this into the VB field:
Worksheets("Analysis").Range("I4").Value = Worksheets("Amortization").Range("D4")
Worksheets("Amort_Data").Range("N2").Value = Worksheets("Amortization").Range("L9")
Worksheets("Analysis").Range("I6").Value = Worksheets("Amortization").Range("D14")
Worksheets("Amort_Data").Range("P2").Value = Worksheets("Amortization").Range("L10")
Using the second line as an example, once the button is clicked it will copy data in cell D$ from my "Amortization" sheet to cell I4 on my "Analysis" sheet. ie if you haven't changed sheet names you would be copying from sheet 1 to sheet 2. For each different cell that you wish to copy to another sheet, you must enter a new line. In my program I have created I have 1 click button that copies 18 variables from one sheet to another. these all have to be entered manually. This works perfectly for me as I have a Loan Amortization on sheet 1 and I wish to copy data outputs from sheet 1 to sheet 2. I have 8 different buttons on my first sheet that perform different actions.
If you need more help please reply to my post here!