3 Questions

stevespaulding

New Member
Joined
Jan 29, 2008
Messages
37
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!
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Stormseed

Banned
Joined
Sep 18, 2006
Messages
3,274
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

Banned
Joined
Sep 18, 2006
Messages
3,274
In regards to your PM:

stevespaulding said:
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

New Member
Joined
Jan 29, 2008
Messages
37
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

Legend
Joined
May 15, 2003
Messages
29,536
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

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,511
Messages
5,596,581
Members
414,079
Latest member
Frills

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top