3 Questions

[stevespaulding]

1. Have worksheet that I want to protect so that users can only access unlocked cells, but need to do sort in middle of macro run. Sort will not work if worksheet is protected. Can worksheet be unprotected (without password) to do sort and then protected again? If so, how?
2. How can I lock and/or hide my macro VBA code so that it cannot be seen or inadvertently altered by a user?
3. How can I prevent (or at least control) a user from copying or sending my Excel program (workbook with macro) to someone else without my authorization?

Many thanks!

 

[Stormseed]

Sort will not work if worksheet is protected

Which version of Excel do you use on your computer? In Excel version 2002 and above you can Protect your worksheets with the Auto Filter functionality turned ON. In version 2000 and below, you can use VBA in order to turn ON the Auto Filter functionality through VBA.

can I lock and/or hide my macro VBA code so that it cannot be seen or inadvertently altered by a user?

Yes, you can do this. Press Alt+F11, Tools --> VBA Project Properties --> Protection tab --> Provide a password.

I don't really understand your third question.

 

[Stormseed]

In regards to your PM:

Thanks for your reply. Regarding the 3rd question: I would normally provide my Excel program to a user as an attachment in an e-mail. I don't want that user to be able to give or send my Excel program to anyone else (at least not without my okay). How can I control that? Is there a way to prevent that or a way that I would know that was done?

Hi

You cannot prevent anyone sending the excel file unless you are using a third party software. You can, however, restrict the user from accessing your Excel file. In other words, protect the whole file with a stong alphanumeric password which would be required in order to OPEN or MODIFY your Excel File. Here the 2 terms, namely, OPEN & MODIFY have different meanings. You will get these options in Excel menubar --> File --> SaveAs --> Tools --> General Options.

Password to OPEN:
User will NOT be able to open the EXCEL FILE irrespective of the location of the file unless the correct password is provided at the time of opening the file.

Password to MODIFY:
User will be able to open the file but will NOT be able to modify & save the file with the same name at its current location. The user will be able to modify & save the file with a different file name and to a different location (directory or folder).

Moreover, once you lock the VBA Project with a strong alphanumeric password, be assured that your VBA Project would be safe to whichever hands the Excel file goes into, because the security of these passwords are with 64 Bit encryption and these passwords cannot be broken easily.

I hope this helps you

 

[stevespaulding]

Stormseed (or anyone):

Having trouble implementing your solutions to my first two questions.

1. How does AutoFilter help if I do need to sort (rearrange) data in a range? Is there a workaround so that I can do a sort on data in a password protected worksheet?

2. When I Alt F11, a blank Module102 appears in Visual Basic, but my VBA code happens to be in Module64. With Module64 open, I tried Tools > VBA Project Properties > Protection tab > Checked 'Lock project for viewing' and keyed in password twice, but that doesn't seem to work either.

Any help/suggestions greatly appreciated.

 

[Smitty]

1) If you record sorting when the sheet's unprotected you can use something like this:

ActiveSheet.Unprotect "PasswordGoesHere"
' Sort code here
ActiveSheet.Protect "PasswordGoesHere"

2) You need to close the wb and reopen it for the protection to stick.

3) Unfortunately you don't. Excel isn't a secure environment, nor is it marketed as one. Do a board search for "Distribute workbooks", "Expiring workbooks", "Kill on date", etc. for lots of discussions on the topic. If possible you can also distribute as a PDF.

HTH,

 

[tusharm]

1. Have worksheet that I want to protect so that users can only access unlocked cells, but need to do sort in middle of macro run. Sort will not work if worksheet is protected. Can worksheet be unprotected (without password) to do sort and then protected again? If so, how?
2. How can I lock and/or hide my macro VBA code so that it cannot be seen or inadvertently altered by a user?
3. How can I prevent (or at least control) a user from copying or sending my Excel program (workbook with macro) to someone else without my authorization?

Many thanks!

1. You need to unprotect the worksheet.
2. In the VBE, select the project (workbook), then Tools | {Your workbook's VBA project name| Properties... | Protection tab | check the 'Lock project for viewing', enter the password, save, close, and reopen the workbook.
3. You cannot.

Note that there are any number of programs / code samples floating around that will unlock a VBA project / Excel workbook. So, any Excel protection is superficial at best.