using form to popluate a database

Posted by Darren McCammon on January 03, 2002 3:18 PM

I'm trying to create a form which will automatically populate the next line in an Excel list when the OK buttonm is pushed. The list is used to make a pivot table. I can create the form (with validation drop downs). What I can't do create the ok button which popluates the next line in the excel database. Can anone help?

Thanks,
Darren

Posted by Jacob on January 03, 2002 3:50 PM

Do you want to add the value selected on the form to excel when you push OK?

Try this

On the click sub of the OK button put this
assuming you want to put the value in A1 of Sheet1 and the name is inputted in Listbox1 modify as needed.

Sheets("Sheet1").range("A65536").end(xlup).offset(1,0).formulaR1C1 = Listbox1.value

If this isnt what you needed post a responce with some more info.

HTH

Jacob

Posted by Joe Was on January 03, 2002 7:57 PM

Try this:
Build a data list to the right of a sheet (to the right of the visible screen) the lables must be in the first row. Then select a cell in the table where data will go, any cell not a lable. Once Excel finds your table after the first use you will not need to select a data cell again. Then move left and select Data from the menu bar then select Form. Excel will automatically build a data input form based upon the lables of your data list. The form has more features than you could easily code yourself. Such as auto delete, filter by criteria and it places your data in your table all by itself. JSW