How to hide/disable VBA interface on protected sheet?

[Kelvin Stott]

Hi,

Thanks for all the help so far! I've now completed my model, but have encountered the following security issue when I try to protect my sheet:

I have added the following code to enable grouping/ungrouping on my protected sheet, but the VBA interface and code can still be opened (and even changed!) while the sheet is protected, so my supposedly secret password becomes visible, which defeats the whole point of protecting it!

Private Sub Workbook_Open()
    With Sheet1
       .Protect Password:="my password", UserInterfaceOnly:=True
       .EnableOutlining = True
    End With
End Sub

So: How can I hide and disable the VBA interface so that none of the macros can be opened or edited while the sheet is protected?

Thanks for any help/guidance on this.

 

[Jon von der Heyden]

You could protect the project.

Open the VBE (ALT+F11)
Tools > VBA Project Properties
Enter password in Protection tab
Check Lock Project for Viewing

 

[Colin Legg]

Open up the visual basic editor
In the project explorer window, right click on your project > properties
Protection tab
Tick the 'Lock project for viewing' checkbox
Type in and confirm a password (remember it)
OK

Save, close and re-open your project. The VBA code can only be viewed if you know the password.

 

[VoG]

In the VBE, Tools > VBA Project Properties, Protection tab, tick Lock project for viewing and enter a password. Save, close and re-open the workbook to see the effect.

Note that this won't stop a determined hacker.

 

[Kelvin Stott]

Woo, perfect - thanks for two very quick and helpful replies!

Project completed!