PDA

View Full Version : Solved: Protecting macro code



GrantLund
08-22-2006, 02:27 AM
Hi All!:hi:

Ok. I have moved everything into Excel from Word and so far so good, however, I want to protect the code and worksheets so that people can't get into the commandbutton and other control toolbox properties and edit the worksheets as well. In Word it is simple - password protect your document. In Excel, that doesn't work. I have tried protecting the sheet, the workbook and locking the objects but that doesn't work - you can still get into the code/userforms etc. How can one protect the code to stop people from editing it?:help

Second thing: I have hidden all worksheets and have a main worksheet which if you click opens up the "hidden" worksheets and hides the main worksheet and vice versa. However, when I protected the workbook with structures and windows options then get an error in the macro because it sees the unhiding as an edit function I presume. If I just click on structures OR windows options then don't get a problem with the macro. Should I just accept this level of workbook protection (ie. only either structure or window option but not both) or is there another way?:think:

Rgds
Grant

ALe
08-22-2006, 02:47 AM
first: in VBE select tools-> Properties ->Protection and insert your password. In this way you protect your code (close the file saving and open it again to test it)

second: in your code you have to insert thisworkbook.unprotect before you unhide/hide a sheet and thisworkbook.protect after you unhided/hided a sheet

GrantLund
08-22-2006, 03:07 AM
Ale,

Thank you. The first bit works (although still dunno why Excel has to work differently to Word). The second gives a debug error as it wants the password (I password protected the workbook). I presume I need to insert some command with the correct password in it but not sure what this code would be. ie. when unprotect, assign password (say X for example) and compare with actual and then unprotect?

Rgds,
Grant

ALe
08-22-2006, 03:26 AM
thisworkbook.unprotect("yourpassword")
thisworkbook.protect("yourpassword")

GrantLund
08-22-2006, 03:28 AM
Oh, one thing I forgot to mention. Protecting a workbook or worksheet doesn't appear to stop anyone from being able to delete commandbuttons or other control toolbox elements (if they go into design mode) it only freezes cell info (not even drawing elements). It was so simple in word *sniff sniff*

Rgds,
Grant

GrantLund
08-22-2006, 03:37 AM
Ale,

Thanks - got that working now. Only thing still outstanding is the deletion of command buttons query but everything else is working ok.

G:friends:

johnske
08-22-2006, 04:28 AM
Oh, one thing I forgot to mention. Protecting a workbook or worksheet doesn't appear to stop anyone from being able to delete commandbuttons or other control toolbox elements (if they go into design mode) it only freezes cell info (not even drawing elements). It was so simple in word *sniff sniff*

Rgds,
GrantIn the "ThisWorkbook" code module
Option Explicit
Private Sub Workbook_Open()
Application.CommandBars("Control Toolbox").Controls("Design Mode").Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Control Toolbox").Controls("Design Mode").Enabled = True
End Sub

Bob Phillips
08-22-2006, 06:47 AM
In the "ThisWorkbook" code module
Option Explicit
Private Sub Workbook_Open()
Application.CommandBars("Control Toolbox").Controls("Design Mode").Enabled = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Control Toolbox").Controls("Design Mode").Enabled = True
End Sub

I would suggest that workbook Activate/Deactivate events should do this also.