Entering information into a form and returning it in a spreadsheet database

**Nic Labs** · 07-21-2010, 06:37 AM

I am trying to get information that is typed into a form in Sheet 1 to reflect in sheet 2 and then save it.

Sheet 1 looks like this
A B C D
1 Name: _____X_______ Surname: ___Y________
2 Adress: ____Z_______ EFR num: ___A________

Sheet 2 looks like this

A B C D
1 Name Surname Address ERF num
2 ___X____ __Y_____ _Z____ ___A_____
3 ________ _________ ________ _________

I can do this using a vlookup formula but if the information changes on sheet 1 then the information changes on sheet 2. I need the information on sheet 2 to remain there and to allow for adding new information using the same form on sheet 1. The new information on sheet 1 must then be added to line 3 on sheet 2

**realniceguy5000** · 07-21-2010, 06:46 AM

Originally Posted by Nic Labs

I am trying to get information that is typed into a form in Sheet 1 to reflect in sheet 2 and then save it.

Sheet 1 looks like this
A B C D
1 Name: _____X_______ Surname: ___Y________
2 Adress: ____Z_______ EFR num: ___A________

Sheet 2 looks like this

A B C D
1 Name Surname Address ERF num
2 ___X____ __Y_____ _Z____ ___A_____
3 ________ _________ ________ _________

I can do this using a vlookup formula but if the information changes on sheet 1 then the information changes on sheet 2. I need the information on sheet 2 to remain there and to allow for adding new information using the same form on sheet 1. The new information on sheet 1 must then be added to line 3 on sheet 2

Hi,

It may be easier if you post a example workbook for us to look at.

Thank You, Mike

**royUK** · 07-21-2010, 07:26 AM

There's a coupleof examplesthat might helphere

http://excel-it.com/vba_examples.htm

DatebaseForm
Contacts Form

**Nic Labs** · 07-22-2010, 05:21 AM

I am unable to attach the worksheet as it is over 1mb big but i have no idea why. I have deleted all the macros that i have tried to create and all i have are vlookup formulas

**Nic Labs** · 07-22-2010, 05:32 AM

Ah ha! I have made it smaller and here it is

It has 3 sheets: Input Screen; Res Listings & Print screen

Input screen has a form that needs to be filled in. This information must then be saved to Res Listings

Res Listings is a summary of all the forms filled in on Input screen.

Print Screen pulls information from a specific line in Res listings and populates all fields required. This is done by typing the corresponding Property number in cell b1 of print screen.

The problem lies with the input screen and the Res Listings.

**royUK** · 07-22-2010, 05:51 AM

Take a look at the Contacts form in the link above

**Nic Labs** · 07-22-2010, 06:16 AM

Thanks Roy

That will really help me. Now the next thing is for me to learn the macro.

Mind if i ask a few questions.

I see you have defined some variables wsData; wsForm and rNextCL
why have you defined wsData and wsForm as sheet 1 and 2?

I understand rNextCL as a variable but could you explain this code

Please Login or Register  to view this content.

If i understand this correct it is looking for the next open row in sheet called data. I don't understand this part "Cells(65536, 1).End(xlUp).Offset(1, 0)"

Is (65536, 1) a range? If so what range, something like (A1:X21) ?

And lastly what does end(xlUp)

I appreciate your help

**royUK** · 07-22-2010, 06:53 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**royUK** · 07-22-2010, 03:47 PM

Originally Posted by Nic Labs

I see you have defined some variables wsData; wsForm and rNextCL
why have you defined wsData and wsForm as sheet 1 and 2?/

they are set to Sheet1, Sheet2 to identify them & refer to them in the code
see http://excel-it.com/SheetName.htm

I understand rNextCL as a variable but could you explain this code

Please Login or Register  to view this content.

it finds the next empty cell in Column A,working from the last row(in Excel 2003 & earlier)

If i understand this correct it is looking for the next open row in sheet called data. I don't understand this part "Cells(65536, 1).End(xlUp).Offset(1, 0)"

It is searching upwards to find the last cell containing data in the column,it then uses Offset to get the next empty cell

The sheet is not necessarily called Data it is the sheet defined as wsData

Is (65536, 1) a range? If so what range, something like (A1:X21) ?

It's last cell in Column A - A65536

And lastly what does end(xlUp)

It sets the search direction upwards, that way it avoids stopping partway through a table if it encounters an empty cell searching downwards.

You can find most of these in the VBA Help files if you highlight the words & press F1

**realniceguy5000** · 07-22-2010, 04:05 PM

Hi,

I have done some work on your problem and have come up with this soultion.I'm sure there is a better way, but this works.

I have attached a sample workbook as a test to see how it works for you.

Some things to do if you dont use the testbook:

1st you should make sure all the names from sheet(1) Match the headings on sheet(2)
Example input screen cell "A8" = "Address:" Should Match worksheet "Res Listing" Cell "C2"
2nd on sheet "Res Listing" you want to hilight cells A2 to BB2 and name the range"INFO"
If you dont already know towards the top left of your spreadsheet there is a little window that tells you what cell you are on. After you hilight the cells just type "INFO" in the box and hit enter.
3rd Just make a button on your input sheet and to call my script.

Here is the code: And the testbook is attached.

Please Login or Register  to view this content.

Let me know how it works for you.

Thank You, Mike

**royUK** · 07-23-2010, 04:01 AM

Mike,I haven't tested your code but it would run much better if you didn't use Select

**Nic Labs** · 07-28-2010, 04:18 AM

Thanks guys

You have solved my problems. I have used the following coding

Please Login or Register  to view this content.

This is the simpliset for me as this is what i understand at the moment and if anything changes i know how to change it.

Thanks for your help.

**Nic Labs** · 08-16-2010, 05:05 AM

I would like to make an addition to this code but im not sure how to add it in or where to add it into. This is part of the Code below.

Please Login or Register  to view this content.

Just before

Please Login or Register  to view this content.

I would like to insert a conditional if statement for instance

Please Login or Register  to view this content.

Thanks

Entering information into a form and returning it in a spreadsheet database

LinkBack

Thread Tools

Rate This Thread

Display

Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Re: Entering information into a form and returning it in a spreadsheet database

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions