open another workbook and close it after writing data

hi all

i am currently making a survey through VBA (in work hence why VBA not VB) ...i have written the code so it can collate the data via checkbox1.value=true then (range......etc.) +1

however what i need to do is collate the data onto a seperate excel sheet and close (and save changes obviously) when the data has been written (need to do this as in work when anythin is on the shared drive it becomes read only when more than 1 person has it open so the point of the survey would be pointless if only 1 person can access it at a time)

i cannot find any code to load another excel worksheet write my data to the assigned cell and then save and close.

can anyone help me please?


i'm not by any means a pro so the simpler you can make this for me the better please don't assume i know what i'm doing as i only know the basics :lol:

There's a few options avaiable, but I wonder if Excel is the right tool for this?

How much data is entered by the users on each occasion, and what Excel-specific functions do you apply to it?

well what i'm doing is if checkbox1.value = true Range=(a1) +1 (not that exact code but it's along those lines

it's working perfect for what i need. just need it in a seperate workbook really.

due to me doing this in work excel is the only option unfortunately as i dont' have Access to put the data into a database

Try and expand on what your users need to do. Will they need to use this more than once? Sever times a day? Will the questions change?

Please outline your project in detail...

hope this explains a bit better

got 7 forms
each with 5 questions on each form
each question has 5 answers (strongly, agree,unsure,disagree, strongly disagree which are done through check boxes at the moment)

so for each Submit button i need it to pick up the 5 answers (which i've been doing with If statements at moment and range +1 statements)
i want the results to be saved as numerical so adding 1 every time somone chooses strongly agree it adds 1 each time.

there will be 100+ people having to use this survey throughout a few day period.

due to the shared drive in work being read only when you open a file more than once, that is why it's imperative that the data is saved to another workbook and then closed each time.

this will probably be sent out to 10 ppl at once so this is the easiest way for this to work in excel.


so to clarify there are 25 check boxes per form (5 questiosn and 5 possible answers) and the submit buttons needs to add 1 to the appropriate cell so i can collate the data to make charts / reports etc.

each person would only need to use it once.

If you feel there would be an easier way to do it (drop down box etc.) feel free to suggest and alter accordingly


like i said i know about VB but by no means am a pro so i hope that is the information you need if not just say wat else you need so i can provide :)

thanks


just to add aswell i have it running perfectly within 1 worksheet but cannot get the code to save it to another workbook

Ok, this sounds like a one-off, so you need the basic functionality without spending alot of resources on somthing which will be discarded after use.

That being the case, I suggest you scrap the userform approach entirely and, yes, use in-cell drop-downs to manage your responses. Create a template (xlt) and have each user complete a copy, then save the file (you can do this using vba if you need to follow a rigid procedure) to a central location.

Once all the files have been submitted, you can easily import all the completed sheets into a single workbook within which you can analyse your results by using formulas which sum across sheets.

Sound feasible?

how would i save the data within VBA? i know how to do it within the cells of the excel sheet that is linked to the program but is it possible to store the data within the actual program? (this data must be hidden to stop tampering of results)

please can you include some code to write to another workbook within VBA so i can have a play around and see how i can do.

thanks for your help so far.

For the answers, I would go with the dropdown lists or option buttons that don't let the possibility to answer multiple choices for the same question.

To save the answers, I would go with simple TXT file(s) if database is not possible...
It could be one (with Append) or multiple (with Output) files. It could also be done with Binary, so it's harder to read and/or modify.
When you want to compile the answers, just import that or these files in Excel.

how would i save the data within VBA?
Look up the 'SaveAs' method in your Excel VB help. The approach I suggest does not "save data to another file". Instead each user saves their responses in a separate workbook. Once all the files have been submitted, then you can easily import them all into a "master" workbook which can use forumlas to sum up the responses from each sheet.