Protect Worksheet before Emailing

Hi there,

Try adding the highlighted line to your code:

Please Login or Register  to view this content.

Note: I haven't tested your code fully as I don't have Office 2007 installed, but I think that the line shown in red above will cause your SOURCE workbook to be added to the email as an attachment rather than the COPY which was created by the SaveAs command. To attach the COPY workbook you should use:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Thanks Greg M! It didn't work right away, so I moved that blue line up one so that it protected before the save and now it works great! And the workbook that gets emailed is actually the new temporary one created, so I probably won't touch that other line of code. I don't really know a ton about VBA myself, but it looks like this code sets the name of the temporary file with the name Destwb in this first section here and then just references it down below. I'm not sure, but it works great!

Now that the worksheet is protected, is there a way to also protect the VB window that has all the code in it? My original workbook has this protected (I went into project properties and password protected it) but this temp file that gets emailed is unlocked so anybody can view the code. Is there a way to protect that as well by editing this macro? Thanks so much for your help, it's very much appreciated!

Please Login or Register  to view this content.

Hi again,

Yes, you're quite right - sorry if my suggestion sent you off on the wrong track. (I did say that I hadn't tested the code completely!)

Regarding protection of the VBA code in the copied workbook, I presume the only code involved is the code contained in the VBA module of the copied worksheet itself. I feel the simplest thing to do is to delete the code from that worksheet unless it actually needs to be present in the copied workbook. Would this be an acceptable approach?

If the worksheet VBA code is in fact needed, you'll probably have to create a copy of the workBOOK (with the VBA Editor protected) rather than just the workSHEET you're interested in. After creating the new workbook you can then delete all of the worksheets except the one you want to retain. This approach will ensure that the VBA editor of the copied workbook is protected.

If you need assistance with either of the above suggestions please let me know.

Regards,

Greg M

Oh, ok that makes sense! Yes the code is needed. It's the code that runs this whole process. I have a button programmed to copy the worksheet into a temp file, email it, and delete it. It used to be completely unprotected (the worksheet). So as part of the code (that you helped me on) we protected the worksheet before it got saved and emailed. Since that button is on the same worksheet that's getting mailed and the project is unlocked, the recipient can now view the code, and find the password within there that unlocks that very worksheet. So I'm trying to get that locked. It looks like it's going to be more difficult than I first thought. I'm guessing I'll either have to put the button on a different worksheet as to eliminate the necessity of having the code in that worksheet, or do what you say and copy the whole workbook, then somehow delete all other sheets while leaving the project protected. The reason for the whole process in the first place is to allow the sender to retain confidentiality of the other worksheets, so the recipient can only see the one worksheet. So if we do this approach, we'd still have to ensure that the final product that gets emailed is only that one worksheet. So any way to go about it a different way than the code I posted would be awesome! I'm not sure how to do that though!

Hi again,

Ok - I need to ask a few more questions to be sure I understand exactly how your workbook is set up.

If the code contained in the worksheet VBA code module is just the code needed for copying/emailing/deleting, then it could almost certainly be contained in a standard VBA code module rather than in the worksheet module - in such a case the standard module would not be automatically copied to the new workbook, and we'd just delete the button from the copied worksheet.

If the code in the worksheet VBA module contains no event-related code (e.g. Worksheet_SelectionChange, Worksheet_BeforeRightClick, Worksheet_Activate etc.) the code can almost certainly be transferred to a standard VBA module.

If the code in the worksheet VBA module DOES contain event-related code we need to adopt a different approach. The simplest way is probably to create a copy of the entire source workbook (with the VBA Editor protected), "VeryHide" all worksheets other than the one you want the user to see, and then password-protect the entire workbook so that the hidden worksheets cannot be unhidden.

Does either of the above approaches sound acceptable? Is it possible for you to post a sample copy of your workbook so that I can experiment with it?

Regards,

Greg M

Greg M - Thanks for your response! That makes a lot of sense. I could definitely move that code to a standard module and I'm guessing I'd have to then call it from the worksheet module??? But that last approach sounds intriguing (to copy the entire workbook and veryhide the sheets). This would hopefully leave the project protected. I've posted the sample workbook, and the password for all the worksheets and the project is "blondie." I've used my email address for the "email to linda" button for now. The other button ("Email To:") is for emailing to anybody else. The only difference in the code is it creates an input box to manually type in an email address. Thanks so much!

Hi again,

Take a look at the attached version and see if it does what you need.

The most significant alteration I've made is to change the buttons on the REPBUILDER worksheet from ActiveX controls to Forms controls. The buttons appear identical to the original ones, but this change allows me to store the code associated with these buttons in a standard VBA code module rather than in the worksheet code module.

When the REPBUILDER worksheet is copied into the new workbook, the VBA code is NOT copied along with it. To avoid error messages caused by buttons "looking for" VBA code which no longer exists, the three buttons are deleted from the copy of the worksheet before the workbook is saved.

As a result of the above, the workbook which is attached to the email contains only a single protected worksheet, no control buttons, and no VBA code.

You'll probably need to "uncomment" your code which does the PasteSpecial operation to convert formula results into values, otherwise references to cells on the INPUT worksheet will be displayed as #REF! errors, and the cell containing the =NOW() formula will continue to update.

Does this approach meet your requirements?

Regards,

Greg M

Greg -Thanks so much, this is fantastic! Everything works great, and I had no clue that a recipient wouldn't be able to see any modules of code. I tested it and it works just awesome. The only thing I noticed is that none of the other commandbuttons in the workbook work anymore. Macros are still enabled, I'm not in design mode, and the code hasn't changed. Any ideas? Other than that, I'm very excited, and it works perfectly for what I wanted!

And I didn't get a single spurious email the whole time you were working on it, so nice job!

Hi again,

When I read your last email I checked the original workbook you had sent me, and I found that none of the buttons in that version worked either!

Anyway, I re-assigned the various buttons to their respective macros and everything now seems to be working ok in the attached version.

Please let me know if it's working correctly for you or if you need any further assistance.

Regards,

Greg M

Yes it does, thanks so much for all your help!

Hi again,

Many thanks for your feedback - I'm very pleased that I was able to help.

Regards,

Greg M