Hi everyone,

I need your help . I've created an excel document with 3 sheets, a user form with 3 check boxes and 3 text boxes (this document should be like a data bank). I would like to have it like following:

when I check the first check box the right sheet should be activated (I've got this working). Now I want that the data I want to export should go to the right sheet (activated one )and to the right column in the next empty row (line). Every sheet (3 sheets) has 9 columns and 300 rows ( I know how to export the data from textbox to the sheet but I don't know how to send it to the next empty column/row)
The first column is the number of the row and I would like it to update itself to the next no when I enter the data ( if the last no is 10 then the date should be exported to line 11 and the column no should be 11 automatically).

Could anybody help please

Thanks a lot

Madona

You need to write a sub that sets the focus on the required sheet, then loops through all the cells in column A until it finds one that's empty. Then it transfers the text over.

Hi Weejas,

Thanks for the quick answer. Could you or anybody help with a VBA Code for that please?

Thanks a lot

Madona

Zip a copy of the spreadsheet and upload it - I'll see what I can do.

Thanks a lot Weejas,

File is attached

Here you go

A couple of pointers - name your controls! It's much easier to move data around if you know what each item is. Also, comments are your friends. One programmer might have been of the opinion that "It was hard to write - it should be hard to understand!" but I disagree. Believe me, you put this down for a couple of weeks and go back to it, and you'll spend the first hour just trying to work out what everything does.

Lecture over.

I've commented out your original code and renamed all the controls. I've also got rid of the frame, and replaced the check boxes with option buttons in named groups. I've also commented out the line of code that close the application after setting the saved status to True - this caused a (brief) loss of data when I clicked "No" by accident. You probably want to revisit this, unless this functionality works in conjunction with something else.

The form now uses the value in grpType (saved in a global variable as a number) to derive the name of the required sheet. It uses the value in grpReason to determine which Reason column requires populating. It then finds the next vacant cell in column A below row four, and generates the next number (either 1 if the cell above is not numeric, or the value of the cell above plus 1). Finally it dumps the contents of the text boxes into the relevant cells in that row. It foes not change the focus in order to do this - it happens invisibly (unless the target area is visible). Enjoy!

THANK YOU, THANK YOU,THANK YOU, THANK YOU,THANK YOU, THANK YOU,THANK YOU, THANK YOU,.............................................. .................................................. ......................
.............................YOU ARE A STAAAAAAAR.

madona

You're welcome.