Code for Experiments

trying to set up some specific code for lab experiments using excel and VBA.

sample is provided.

thanks


'==============
I want to set-up labs on spreadsheets, that force saves upon completion and send those saves to a specific address (say my email address: or better yet, a specific folder at my work.
I have attached a sample of what it is I am trying to do.
I have an Excel file, which would contain 3 worksheets:
Any sheet that is protected by password, the password is 123
Worksheet 1 is just instructions and would be made available at all times, but would be protected using a password.
Worksheet 2 would be for their data entry. Only specific cells would be un-locked. I would like a prompt (button) asking the student for his/her Name, then student Number prior to gaining access to this sheet. I would also like to have that info placed in the appropriate cells C3:E3 and C4:E4. They should not be able to close the file, without a ‘forced’ save via some VBa Code (if this is possible). So they would have 2 choices; 1)Close the file upon completion which will force a save 2)Check the solutions sheet upon completion, which will prompt them and force a save. I would like it for them to not have access to saving the file to wherever they chose, and would like for the program to only allow a save as mentioned above,either address or folder at work.
Worksheet 3 would only be accessed after a prompting the students if they are sure they are done, and that their work is final if they wish to check the solutions. This action should save their work to the above mentioned.

Have a look at the attached book

On opening book it hides sheets 2 & 3 - Can only be made visible via VBA code

Button on sheet1 opens a form for student to provide name & number. If name already entered in sheet2 c3 form does not open.
Code for OK button checks both name & number sulpplied before making sheet 2 visible & adding name & number to sheet2

Sheet2
If cells c3 or c4 are select a macro changes selection to another cell
Sheet has 3 buttons 2 visible, i invisible which is made visible once the save button is clicked.

There are no code to handle saving workbook at the moment.
Saving to a mapped drive & folder would be the easiest option.

I do not have time to continue on it at the moment - will have later today

Try this workbook - It should work as required

Save commands have now been coded

The module1 sheet has settings for sheet names, Path to save file to when save button on sheet 2 pressed.
The file name when saving is a combiation of the workbook name & the students name as entered into sheet2 C3

The only way to save the workbook is via the save button on sheet2 or for yours & my pupose a save macro on the module1 sheet. This sheet also has a macro to clear students name & number from sheet2.

You will need to password protect the vba project as well as the worksheets

Please make sure you test everything that you can.

Good to see you picked up my typo

This disables the button so it can not be clicked - true means button can be clicked

Please Login or Register  to view this content.

This hides the button - true means button is visible

Please Login or Register  to view this content.

There are commands on button2 macro to make button enabled & visible
I could have just used 1 of the 2 commands, but played safe & used both or set up a blank book with the buttons & codes to act as a template

To assist in applying to other books is why I have used the Const settings on the module sheet. Perhaps you could just redisgn sheets so you do not have to worry about setting up the buttons & the code for each book.

To change the text on buttons
Unprotext sheet
Open Control Toolbox menu
Click on Design mode button (triangle icon shap)
When you run mouse over button pointer should change to a cross hair ( + )
Right click on button
Select Properties
Edit Caption Entry
Change Fore colour to change text colour
Once changes are made
ReClick on Design mode button (triangle icon shap)

With the saving do you mean that you can save it at any time before student info is entered and by using any save method then we have a major problem & it should not be doing that.
or do you mean that you can save it at any time by using the save button on sheet2
If it is at any time by using button on sheet 2 then additional code can be added to test if certain cells have values before allowing a save. You would need to let me know the specifics

Question
worksheet protection are 'easy' to crack. Is this true
Answer
Yes - a google search will turn up hundreads of links

Q
if a student is clever enough, and saves the "Workspace"
A
I have no experience with workspace saves

Q
If the final save is directed to a mapped drive, if that student is not connected to the internet, will the solutions sheet appear?
A - Did not think of this. If save directory is not available solutions page will appear. I have modifed the code

Before I paste the modification
As the book is only allowed to be saved once Should the solutions page be made visible when the file is saved - instead of via a button
Because the book has changed after it has been saved Excel asks you if you want to save when you close the book - Macro code stops the save if you answer yes

MBS

Looks like cell formatting has changed

disable Range("c11").Select in the Worksheet_SelectionChange macro
select c3 - set cell formating to general
select c4 - set cell formatting to number & 0 decimal places

re-activate Worksheet_SelectionChange macro

Where are we up to with security on the workbook
I do have some code that can encrypt & decrypt data that could be modified to work with your solutions sheet.
Another option is to have the solutions stored on a seperate file that gets imported into the workbook when the view solutions page is clicked

The above suggestions are not perfect as passwords to the vba module can be cracked.

In The attached workbook I have changed the formatting as mentioned above
Removed the view solutions page button & add code to the submit button macro to display the solutions page
Fixed a bug in the save commands of the submit button macro

MBS

When I save the file using the save button on the Assignment sheet it displays the Solutions page after it saves.

Can you give any more info on the failure.

I can not help you re your other query

I left a stop command in one of the macros
I use stop commands when testing & debugging.


This version has the stop command removed

MBS

I well on the way to changing the code so that it sends an email & remove the save option.

But I thing I have realized is that the solutions sheet can by copied to another workbook and then saved in the new book

I am working on a solution for this problem.

What version of Excel are you running?

To test if some added code I wish to use can you put this macro in a new workbook, run the code and let me know if you get an error message or a message box displaying MBS DoNotSave

Please Login or Register  to view this content.

MBS

It is possible to hide the Exam sheet when the solutions sheet is displayed.

I have thought of another problem & can not figure an easy way to resolve it.

Once the solutions page is displayed the student could easily copy the sheet or part of the sheet to another workbook or even just paste it straight into an Email message.

How does the above problem fit in with security of the workbook & keeping the solution page available only to students that have submitted their answers to the exam?

MBS

try this version

I beleive it does everything you want to it to do

MBS

you are not running the latest version I posted 2 days ago

You are running the previous version in which I had accidently left the stop command in it.

You have 2 options
Delete the Stop command then
Pressing F5 will make the macro continue running.

or use the latest version

mudraker,

could you please take another look at the last sample you sent and explain to me how I should get the program to send me an email when the 'autosave' button is clicked. I seem to be stumped as I cannot get it to work properly. I have tried to put in a valid email address into the module, and when I run this part of the code seems to need to be debugged.

Please Login or Register  to view this content.

I appologize if I'm being a nuisance, but I just can't seem to get it.

thanks again,

MBS

MBS
No trouble
I need to apolagize. It looks like I missed your previous reply

I replaced [email protected] with my email address & it worked ok.

The code runs ok even with an invalid email address without displaying an error message.


What version of Excel are you using.

In Excel under File menu > Send To do you have options to select Mail Recepients
Their may be several diffent Mail Recepients

That will do it. No Outlook no email can be sent.

We are using the same version of Excel.

Changes made to this book

No longer displays Solution page if mailing cancelled by using no button or any other mail failure (can not fully test on every mail failure)

When opening the workbook it now displays solution page if experiment page has student name & number

Can no longer save workbook except via SaveWorkbook() macro on VBA sheet Module1. Previous settings allowed workbook to be saved after it was emailed.

Keep testing & looking for problems as you have been doing.

What version of outlook are you using?
You are trying the latest version?
I will re-upload my latest version. I have added date & time to the front page that I updated this version

The emailing code is still the same as the previous version.

I am using Outlook 2003.
When I click on the Submit button I get the dialog box asking if you want to allow sending the email. The options are Yes, No & Help buttons
Selecting Yes - the email is send , The experment sheet is hidden & the solution sheet is displayed.
Selecting No - The email is not sent, The experiment sheet remains visible & the solutions page remains hidden.

If you are still having problems then I will have to post a question asking for help to resolve the problem.

Hi MBS

I here I am thinking this thread was long done with

Replace Private Sub CommandButton2_Click with the following version
Right click on ASSIGNMENT Sheet Tab
Select View Code

Please Login or Register  to view this content.

Replace CommandButton2_Click macro with this version

This version gets sender name from the Student Name field on the ASSIGNMENT sheet as set in the conWsExp constant

The subject message is shown in red within the macro when viewed in this forum

Please Login or Register  to view this content.

I do not know if it can be avoided.
I have no knowledge on this type of problem
I will ask a couple of other posters who may have a better idea

This will check if outlook is running, then offer to open it

Please Login or Register  to view this content.

Roy

Thanks for looking at the thread.
I do not beleive that your suggestion will solve the problem.

The macro unhides a hidden sheet after the workbook has been Emailed.

At present if Outlook is installed on a pc the macro sends the workbook to Outlook and then unhides the hidden page. This occurs even if Outlook can not sent the message.

If Outlook can not send the message then the hidden sheet must stay hidden.

Is there any way via VBA of identifying if the Email has actually been sent before allowing the macro to unhide the hidden sheet.

Or have I missed the point of having the macro force Outlook to be open?

I didn't have time to read the whole thread. I'll have a look at it later.