UserInterfaceOnly in VBA-code and Password Protected Sheets

%3CLINGO-SUB%20id%3D%22lingo-sub-2719548%22%20slang%3D%22en-US%22%3EUserInterfaceOnly%20in%20VBA-code%20and%20Password%20Protected%20Sheets%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2719548%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20sheet%20protected%20with%20a%20password%20(not%20in%20the%20VBA-code%20but%20in%20the%20menu%20of%20Excel%3A%20Review%20%26gt%3B%20Protect%20sheet).%3C%2FP%3E%3CP%3EI%20don't%20want%20the%20password%20in%20the%20VBA-code.%20When%20you%20open%20a%20.xlsm%20workbook%20in%20OpenOffice%20you%20can%20see%20the%20entire%20code%2C%20and%20so%20also%20the%20password.%20Even%20when%20the%20VBA-code%20is%20protected%20with%20a%20password%20in%20Microsoft%20Excel.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20the%20protection%20is%20active%20the%20user%20is%20allowed%20to%20select%20locked%20and%20unlocked%20cells.%20Selecting%20unlocked%20cells%20is%20to%20give%20the%20user%20the%20possibility%20to%20copy%20the%20cell%20content.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20VBA-code%20I%20have%20this%20line%20of%20code%20in%20Workbook_open()%3A%3C%2FP%3E%3CP%3Eworksheets(1).protect%20Userinterfaceonly%3A%3DTrue%3C%2FP%3E%3CP%3ESo%20not%20with%20the%20password%2C%20for%20that%20is%20already%20set%20on%20the%20sheet%20(Review%20%26gt%3B%20Protect%20sheet).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20when%20the%20workbook%20is%20opened%20a%20prompt%20asks%20for%20the%20password%20for%20the%20sheet.%20But%20I%20don't%20want%20that%20to%20happen.%20For%20the%20password%20is%20already%20set%20in%20the%20menu.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20change%20the%20protection%20in%20Review%20%26gt%3B%20Protect%20sheet%2C%20so%20the%20user%20can%20only%20select%20unlocked%20cells%20then%20there%20is%20no%20prompt%20for%20the%20password.%3CBR%20%2F%3E%3CBR%20%2F%3EIs%20there%20a%20some%20VBA-code%20so%20that%20you%20can%20protect%20the%20sheet%20in%20the%20menu%20and%20that%20the%20user%20can%20select%20both%20locked%20and%20unlocked%20cells%20without%20the%20prompt%20for%20the%20password%3F%3C%2FP%3E%3CP%3EAnd%20the%20userinterfaceonly%3A%3DTrue%20in%20Workbook_open().%3CBR%20%2F%3E%3CBR%20%2F%3EWho%20has%20the%20solution%20for%20this%20or%20knows%20how%20to%20fix%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2719548%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

I have a sheet protected with a password (not in the VBA-code but in the menu of Excel: Review > Protect sheet).

I don't want the password in the VBA-code. When you open a .xlsm workbook in OpenOffice you can see the entire code, and so also the password. Even when the VBA-code is protected with a password in Microsoft Excel.

 

When the protection is active the user is allowed to select locked and unlocked cells. Selecting unlocked cells is to give the user the possibility to copy the cell content.

 

In the VBA-code I have this line of code in Workbook_open():

worksheets(1).protect Userinterfaceonly:=True

So not with the password, for that is already set on the sheet (Review > Protect sheet). 

 

Now, when the workbook is opened a prompt asks for the password for the sheet. But I don't want that to happen. For the password is already set in the menu. 

 

When I change the protection in Review > Protect sheet, so the user can only select unlocked cells then there is no prompt for the password.

Is there a some VBA-code so that you can protect the sheet in the menu and that the user can select both locked and unlocked cells without the prompt for the password?

And the userinterfaceonly:=True in Workbook_open().

Who has the solution for this or knows how to fix this.

 

 

 

0 Replies