How to create a form that logs data?

I would like to make a form where a user can fill in information in an easy to ready format, then click a submit button, and have the data moved to another sheet or file from where the data can be merged into another document. Each time the user fills out the information and clicks submit, a new line of the data is created in the destination. Here's a picture to show what I'd like to do:

\1

Any help would be great. Thanks!

Hi

Try this.

Please Login or Register  to view this content.

rylo

Thank you for that. I may be in over my head here, but where would I apply that code? To the button? Would that constitute the macro?

I created the simplified picture to visually explain what I'm trying to do, but perhaps it would be helpful if I just attached the file that I'm actually working on. I put a button in there, but wasn't really sure what to do with it.

Hi

Don't have excel 2007 on this machine so can you please save the file in 2003 format.

rylo

Here's the same file in 2003 format.

Hi

When in excel, hold down the alt key, and press F11. This will open the VBE. Now go Insert, Module and paste the code into the module. Now go back to the spreadsheet, hold down the ctrl key, right click on the button, select assign macro, select the macro bbb. It will now run when you press the button.

However, the macro was built for your post example, and won't do anything much for your working example. Can you advise where the data on Form is meant to be posted on Database? That way we can set up the code to transfer the data to the right cells.

ryl

Wow, thanks for your help. I've saved a new version of the file, but this time, instead of having same data in the fields on the first sheet, I've replaced it with the names of the headers on the second sheet. You'll notice that the three different "Partner/Principal's" have the same headers for each piece of information, so the headers are just repeated 3 times in the next sheet.

Hi

OK try this.

Please Login or Register  to view this content.

rylo

Great! Works perfectly! Thank you.

Could I add a line of code that will tell it to clear the data from the original form after it is submitted and transferred?

Hi

Here's one way to clear it out

Please Login or Register  to view this content.

Unfortunately this will remove the merged cells, so you will have to remerge them again....

rylo

rylo,

could there be a code that would enter a zero, or space to "clear" the data afterward?

Hi

A zero or space doesn't clear out the cell - it inputs a value. The Clearcontents command is the same as selecting a cell in a spreadsheet, then pressing the delete key.

rylo

yes, i understand that it enters a value, but the clearcontents doesn't help if it will un-merge cells.... it seemed a bit counter productive... I was trying to think of a work-around.

Hi

I was being lazy. Write (or record) code to remerge the relevant cells and add to the process.

rylo

sorry for asking all of these really newbie questions, i'm writing forumlas, but the vba and macro stuff is really foreign to me.

If i record the code to do that, would it change the formating of the cells? or would i also have to record the borders and fill color again?

It doesn't sound like 'clearcontents' is exactly like pressing the delete key if it removes formatting as well as data. It would be nice if there was a command that would only clear the data, especially since I would like to add additional formatting to the initial form to make it more user friendly looking.

Hi

Clearcontents doesn't remove the formatting - the merged cells was removed specifically by the command

Please Login or Register  to view this content.

so any formatting would be untouched.

With the merged cells, you can either
1) remove the merging, delete the data and re-merge the cells or
2) include the full merged range in the command.

So if range A1:B2 was merged, and you wanted to remove the data from A1 without touching any of the formatting, you could use

Please Login or Register  to view this content.

So for the earlier code either
1) add new code to redo the merges to the cells or
2) enhance to include the merges cells ranges to the UNION command and remove the line to remove the merging.


rylo

is there a quick little macro that you could write to clear every un-locked cell on a sheet, but keep the formating and data / texts in protected cells?

So it should look like this to preserve the merged cells?:

Please Login or Register  to view this content.

That works great--thanks for the tip in simplifying the code. And thank you for all your help.

For anyone else that might be interested, this is the code I ended up with:

Please Login or Register  to view this content.