SetPassword shold not be removed by others- for excel sheetSet

[Ramachandra1]

Hi,

I set password through File--->Protect work book-->Encrypt with password option like 1234

Through File--->Protect work book-->Protect work sheet option like ABCD.

I shared my file to colleagues and disclosed password 1234 to open . They are able to change the password / remove the password 1234 which i sen through same option "File--->Protect work book-->Encrypt with password"

My requirement is they should not change / remove the password which i set as 1234. How to achieve this?

Regds,
Ram

 

[mole999]

you gave them the password, so you need to find out why they needed to remove the password, it may improve your book if they can do everything you expect them to do without removing. You can use that to ensure its thoroughly tested, before improving your security

 

[Trevor_S]

As mole999 has said, might be useful to know why they need to unprotect. But if its just that you're concerned about an unprotected version being saved somewhere where it might be accessible by others, you could perhaps include the following line in a BeforeSave macro in the workbook:

ActiveWorkbook.Protect("1234")

Then protect the macro with a different password that only you know. This should ensure that if they unprotect the workbook, it is protected again if they save it.

 

[Ramachandra1]

Hi , Thanks for your reply

Could you please reply for the below queries

1.)They want to use without password hence they will remove , so I have to stop it . It should be always with password.

2) How to protect the macro with a different password. Please explain the process

3)in the excel sheet i pressed Alt+F11 and i pasted your code. Whenever i save it is asking macro free sheet will not save yes or no , i have to click no then what format i need to select ?

4)If the person who want to remove password, if he does not enable macros , will it work?

5)If enable also , if he click save as option and save the format as .xls then , password will be enabled automatically?

please provide me complete step by step , at any cost the receiver of the file should not change/remove the password?

 

[mole999]

2 IS ActiveWorkbook.Protect("1234") as part of the workbook_before save module

i suspect though that the password is on when they open the book which is why hey want it removed, as every log on causes a delay

so protect your specific worksheets and your code

starting in safe mode disables the macros so they could get in part way

if you research these steps which will help you learn

have a routine that loads a blank page and then causes all other worksheets to be forced to xlveryhidden as the work book is shut down properly with macros enabled (this should mean that opening without macros presents a blank sheet)

in the workbook open event, VBA to unhide hidden sheets and hide the blank sheet (this ensures that the workbook only operates with macros enabled)

get away from xls, its limiting to excel 2003 and you loose all the extended functionality of later versions

you will need xlsb or xlsm file format

 

[Ramachandra1]

Hi ,

Thank you for your quick reply..

I am new to macros, Could you please provide me the necessity code which i need to include in my sheet , along with the protecting macro

Thanks ,
Ram

 

[Trevor_S]

My suggestion was a macro like this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.Protect("1234")
End Sub

However, this would not work if users opened with macros disabled.

Therefore this could be combined with using xlVeryHidden for all except one (blank) sheet - which you would need to add. This would hide all sheets in a way that could only be unhidden using a macro, which prevents users from unhiding if they have opened with macros disabled. A macro would also be ran when the sheet was opened (with macros enabled) that unhid the sheets with your data, and hid the blank sheet. Macro codes are below. I've called the blank sheet "Blank" and data sheets "Sheet1" and "Sheet2". Change sheet names as appropriate, and add/remove lines if you have more/less than two data sheets:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.Unprotect ("1234")
Worksheets("Blank").Visible = True
Worksheets("Sheet1").Visible = xlVeryHidden
Worksheets("Sheet2").Visible = xlVeryHidden
ActiveWorkbook.Protect ("1234")
End Sub


Private Sub Workbook_Open()
ActiveWorkbook.Unprotect ("1234")
Worksheets("Blank").Visible = xlVeryHidden
Worksheets("Sheet1").Visible = True
Worksheets("Sheet2").Visible = True
ActiveWorkbook.Protect ("1234")
End Sub

This would mean that when the user saved it, they would no longer be able to see the data sheets if they wanted to continue working. They would either have to close and re-open it, or you could put the following macro in the section for the Blank sheet (not the This Workbook one), which would run the macro to unhide the data sheets if the user clicks to any other cell on the blank sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveWorkbook.Unprotect ("1234")
Worksheets("Blank").Visible = xlVeryHidden
Worksheets("Sheet1").Visible = True
Worksheets("Sheet2").Visible = True
ActiveWorkbook.Protect ("1234")
End Sub

To prevent users changing the macros, you can protect the macros by right-clicking on VBA Project(workbook name), probably near the top left, select VBAProject Properties, then Protection Tab. Check Lock project for viewing, and insert a different password that only you know, then click OK. Others, even if they open your spreadsheet, won't be able to view/change the macros.

As suggested by mole999, workbooks with macros should now be saved as .xlsm, rather than the older .xls format.

 

[mole999]

There is no point in having a workbook password if they want access to it and you give it out, makes sure your vba module has a protection code