EXCEL : Data Input FORM DESIGN

  tykesutd 11:50 08 Nov 04
Locked

Hi

I am using Windows XP and Excel 2003.
I have designed a form that will enter data into designated cells in an Excel worksheet.
What I now want to do is to automatically enter new data onto the row below the last row that has been used.
Can anyone help.
Thanks
Allan

The easiest way is to use an array of variant stature, so that each element that you want on your database row is in the array and populated from the form. The next row is found using:

Nextrow = Cells(Rows.Count,1).End(xlup).Row + 1

and the array contents transferred in a simple loop. If you do not need to have the information in your dedicated cells then you can dispense with them in favour of the array.

If you need any more help of a more specialised nature then feel free to contact me direct either via the envelope or messenger. MSN address [email protected]

Allan,

Having seen an example of the worksheet, the problem was caused because you have locked the output of the textboxes on the form to specific cells on your worksheet using the textbox ControlSource property; in your case you have locked them to Row 2 of your database so every change on the form will be reflected in row 2 for evermore.

There are 2 solutions to your problem, the first is to change the linked cells so that they are outside of your database such that when you press the OK button on your form these new cell values are then copied using VBA to the next available row in your database.

The second is to remove all of the links in the ControlSource property and replace them with an array of values that can again be transferred to the database once the OK button is pressed.

I used to favour the array method because in the past the ControlSource property had a bad reputation - I know not why, perhaps VoG might know - but certainly now there appears to be no problem in using it. It certainly saves on the coding required to transfer data. :))

I suggest that you insert a new row above your database and link this row to your textboxes, you can then hide the row if you wish using code to transfer data to or from row 1 as required. An example below assumes that Nextrow is the next available row in your database.

==============

Sub AddCar()

With Sheets("Cars")

For Cnt = 1 to 16

.Cells(Nextrow, Cnt) = .Cells(1, Cnt)

Next Cnt

End With

End Sub

==============

HTH

This thread is now locked and can not be replied to.

New Google phones UK release date | Pixel XL price, new features, specifications: Pixel X and…

1995-2015: How technology has changed the world in 20 years

This is the future of TV, VR and holograms

iPhone 7 review: Enhanced cameras, a refreshed design and water resistance make the iPhone 7 an…