Protect the Sheet!! 1

[simoncpage]

I want to be able to capture when a sheet/workbook is unlocked so that I can run a sub that will delete some crucial coding and thus protect my code as the protect in excel is a joke. Any help with this would be very useful...

Thanks, Simon
ps How is the best way to hide macros. I have a draft model of a sheet which has macros in it but they do not show in the vb editor. Is this because they have been veryhidden in the coding of a sheet? (excel 5 I think) or is there another way?

 

[oharab]

There is no "OnUnprotect" method of a workbook or worksheet, so you can't do what you want.
What you could do is password protect your VBA (Tools>VBAProjectProperties...) so that your friend cannot examine the code you have written.
I'm afraid I know nothing about Macro sheets, so can't help you with that, but if you create functions instead of subs then they will not appear in the Macro window if you choose tools>macros.

Sorry I can't be of any more help

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[simoncpage]

The tools protect is a joke it a sinch to force unprotect it and there are loads of add-in on the market that do this too, Im not protecting the sheet from friends more form company rivals so it needs to be secure.

 

[Loomah]

Simon
This is a half answer to your second question as I don't know how macro sheets transferred into later editions of XL.

However, if your draft model was developed in XL 5 or 7 there was no VB Editor. Instead there were module sheets in the workbook for, well, modules and also dialog sheets for the dialog boxes (no forms).

Like I said I don't know how the modules transferred into XL 8, 9 or 10 but if they remained as sheets they could well be hidden. Check by using the Sheets collection rather than worksheets as below

Sub test()
Dim sht
For Each sht In ThisWorkbook.Sheets
sht.Visible = True
Next
End Sub

As I'm guessing here would you let me know how you get on?

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[oharab]

Protection is only as good as the weakest unProtector. Given enough time and enough money, almost anything can be cracked.
I have several free sheet unprotectors that unprotect all sheets, including macro sheets, within a second or two. Finding a utility to crack the VBA password, whilst not impossible to find, has been a lot harder. The only options I have found I have needed to pay for.
You may be able to develop a COM addin that does what you want to, but that will rely on the person you want to protect the file from actually having the COM: Not very likely!

Finding an uncrackable, unbreakable protection system has been a holy grail for a long time. If you find a way, don't tell anyone here. Patent the method and take it straight to Microsoft!
The only other thing you will can do is trust your users. Make them sign a non-disclosure document that they won't take your software elsewhere, then sue the Ass off them and the company stealing it if they break it!

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[simoncpage]

Loomah,

I was going to try this as I did that for another old model I was looking at - but what I want to know is how I can recreate this old excel5 way of adding in coding to a sheet. As this seems like a more secure way of protecting the coding as you have to look into the sheet which can be coded in a very unorganised manner making it hard to work out.

using this...

MsgBox "There are " & ActiveWorkbook.Excel4MacroSheets.Count & _
" Microsoft Excel 4.0/5.0 macro sheets in this workbook."

...you can tell if any exist

There is an hide option called veryhidden or something like that where by the sheet does not appear on the menu unhide sheet or in the vb editor.

Do you ever have to protect coding in any workbooks and if so how do you do it securely?

thanks
Simon

 

[simoncpage]

Hi Ben

Holy grail indeed I have only been programming in vba for about a year now but its only now that I have written some really good models that another company have been using that I have realised how unsecure excel really is and its a really pain in the arse!

Ideally I would design them in vb but the problem is that most of the people recieving my models have thin clients and its another pain in the arse having to deal with all the techs to install the program.

The non-disclosure thing in theory is a good idea in practice not so good as it is difficult to find out sometimes whether a file has been tampered with and reused. Hence myu search for an onunprotect type statement.

I have come up with a module that sends an email with system info/username once it realises that a sheet is unprotected but this relies on the macros being enabled and that they run code when the sheet is unprotected also - not very tight.

I will continue with this as im sure there is some light at the end of the tunnel!

thanks Simon

 

[Loomah]

Simon
I can't see any way of adding a code module to a workbook as a sheet anymore. That's not an Excel4 macro sheet or an xl5 dialog sheet both of which are still available (as of xl2000). If you are in fact talking about xl4 macros, as opposed to the old code modules, that were infact sheets, then I'm not gong to be able to help much as I only started using xl (tentatively) at v5 when vba was introduced.

I'm not in a job that requires any programming skills so, no, I don't have to protect coding. However, I am sure that it is reasonably well protected from viewing within the vbe if your password is sufficiently strong. Make it 10 characters, alpha-numeric and throw a symbol in for the heck of it.

Either way, the protection is better now than it was in xl7 where all you could do was protect the code sheet to prevent editing, hide it then protect the workbook. A real pain in the wotsit if you needed to change the structure of the book as part of the routine.

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[oharab]

Simon,
This is just thinking out loud, I'm not sure how or even if it can be done, but do you think there is some way of encrypting the code, so that even if it was viewable, it would not be understandable.
Perhaps having a keyfile stored somewhere on your network, with the phrase to unscramble the code. It would probably slow things down considerably, but maybe worthy of consideration, if you have the time.
Let me know how you get on, won't you.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[xlbo]

Just my thoughts and something that's worked quite well for me.
Use the "2 workbooks method" ie your main workbook has all it's sheets xlveryhidden and the VBA project password protected
The user must open up the 1st wb with macros enabled which will then trigger the opening of the 2nd wb. If macros are disabled, the main wb will not open.
Your main wb should have this for each sheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If ActiveSheet.ProtectContents = True Then

Else
MsgBox ActiveWorkbook.Close, savechanges:=False
End If
End Sub
This will close down the wb as soon as the selection is changed if protection has been taken off

Admittedly, I don't think most people here are that interested in breaking it but it's a right bugger to overcome.
Also, In the wb_open event of the main wb, include some code to check for the initial wb being open. If not, shut down again. After this check has been made, the initial wb can be closed. this should prevent anyone from just trying to open the main wb (although, if they do, all the sheets should be xlveryhidden anyway)

HTH
Geoff

 

[xlbo]

oops - ignore the msgbox in line 3 of the sub - remnant from testing
Geoff

 

[simoncpage]

Thanks Xlbo thats not a bad idea - im going to try that now!

 

[xlbo]

Let us know how it works out
G

 

[oharab]

But surely if the password protection in one file is a joke, then the password protection in 2 files is still a joke!
I can't see how this solution has any advantage over the others! Forgive me if I'm missing something, it's coming to the end of a very long day! (-:
B ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[xlbo]

Yeh but using 2 wbs makes sure that macros are enabled and then if protection is removed, any selection change on any sheet will result in the wb being closed. Yes it is possible to break it but you'd have to break the password on the hidden code module, unhide all the xlveryhidden sheets and then scrub out all the macros on selection change for each sheet before you could even begin to look at the wb. Basically, my theory is that if someone wants to crack something bad enough, they will....thius just makes it more effort than most people can be bothered expending for 1 workbook
Rgds
Geoff