excel form. Need data extracted to spreadsheet each time a form co

[MikeR-Oz]

HI,

I have produced a form in Excel that uses drop down boxes etc. When someone
comletes the form I would like the information to also be transposed/
recorded onto a spreadsheet that has columns corresonding to all the form
fields and will add a new line for each form so t the end of the day(year) I
can see how many 'activities' occurred for each field etc.

How is this done?

The form is for staff to easily enter occurances but I then need this data
kept for analysis like how many.

Look forward to your kowledge.

Regards
Mike

 

[FSt1]

hi
something like this would work.....
private sub commandbutton1_click()
Range("A65000").End(xlup).Offset(1, 0).Value = TextBox1.Value
Range("B65000").End(xlup).Offset(1, 0).Value = TextBox2.Value
Range("C65000").End(xlup).Offset(1, 0).Value = Cbo1.Value
'clear the form
TextBox1.Value = ""
TextBox2.Value = ""
Cbo1.Value = ""
end sub

add lines for other controls as needed.
you may need to set up the sheet with headers to start'

regards
FSt1

 

[MikeR-Oz]

Thaks - I think!?

Can you explain what this means? What these commands are saying as I have no
idea- it is at al evel way above me or apears to be.

Cheers

 

[Dave Peterson]

Maybe...

And Debra Dalgleish added some stuff here:
http://contextures.com/xlForm02.html
and maybe here:
http://contextures.com/xlForm03.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[FSt1]

hi
the contols in your form all have a name such as
textbox1, textbox2, combobox1 ect. you can find these name in the contols
propertie box. when the form is fill out, each control will have a value such
as
textbox1.value textbox2.value ect. this is what you want to tranfer to the
sheet.
this line....
Range("A65000").End(xlup).Offset(1, 0).Value = TextBox1.Value
tells excel to.....
go to range A6500 then go up to you hit data then drop down 1 cell(which
should be blank) and whatever that cell is, enter the value of textbox1 in
it. as you input data into the form and transfer it to the sheet, the number
of data filled rows grows. the above line would always put data in the first
blank row of the sheet data.
you would need a line like above for each control that has a value that you
wish to tranfer to the sheet.
but simply tranfering the data doesn't clear the form. the data(values)
entered into the form would still be there so you need a way to clear the
form(reset) for the next data input.
this line
TextBox1.Value = ""
set the value of textbox1 to nothing(blank). AFTER the transfer.
you would need a line like above for each control you wish to clear out.

simple. hope i cleared this up for you.
Regards
FSt1