Locking Excel 5.0 VBA

I am currently working on a project using xL 2k. This project is going to be a widespread distribution for use on old machines running xL 5.0. I am trying to lock access to the VBA source code. When I pw protect the module and then save the .xls as 95/5.0 xls it seems to always remove the module's protection.

This file will be handed off via other users that have a newer version of xL who then are able to open the VB editor and browse my code as they see fit. Is there a workaround for this?

As an alternative to locking the module, is there a way to lock worksheets using VBA with a password without making the password visible in the code?

This project is time sensitive and I am at the end of this phase of development. It needs to go out tonight!! PLEASE HELP!

This sounds like an in-house distribution, how persistant are the "bad guys"?

Put the password in a cell on a locked and hidden sheet might work. Using the Rand to change the each time the workbook is opened might be an idea.

Due to payment/compensation issues, I will not be providing anymore assistance after this release. That will cause the "bad guys" to become very persistant.

If I were to put the PW in a locked cell/sheet I would still have to reference that particular range in the code. Once this reference is discovered, couldn't a user type '=[password range]' in any unlocked cell and have the PWD displayed?

You could use this Open routine (together with a hidden sheet, Secret) to change the password every time the book it opened. The VBA protect lines would look like

[VBA]mySheet.Protect Password:=passwordString()
or
mySheet.UnProtect Password:=passwordString()
[/VBA]

In ThisWorkbook[VBA]Private Sub Workbook_Open()
Dim oneSheet As Object
Dim oldPW As String
oldPW = passwordString()
With ThisWorkbook
With .Sheets("Secret")
.Unprotect Password:=oldPW
Randomize
.Range("a1").Value = "xyz" & Left(CStr(100 ^ Rnd()), 5)
.Visible = xlSheetVeryHidden
.Protect Password:=passwordString()
End With
End With
For Each oneSheet In ThisWorkbook.Sheets
With oneSheet
If .ProtectContents Then
On Error Resume Next
.Unprotect Password:=oldPW
.Protect Password:=passwordString()
On Error GoTo 0
End If
End With
Next oneSheet
End Sub
[/VBA]
This in a normal module[VBA]Function passwordString() As String
passwordString = CStr(ThisWorkbook.Sheets("Secret").Range("a1").Value)
End Function[/VBA]

Fantastic! My last question, do you know if xlSheetVeryHidden is xL 5.0 compatible?

Walkenbach says veryHidden is consistant with Excel 97

That security code is easily beatable by the VB knowlegable. For example, they could write and excicute this whenever they wanted to know the password. (Or the IT branch needed it.)

[VBA]Sub ShowMe()
ThisWorkbook.Sheets("Secret").Visible = True
End Sub[/VBA]

In addition, they could search the code since I am unable to prevent access being that I am required to dumb it down to xL 5.0 and type '=secret!A1' in any unlocked cell to display the password.

So, back to the drawing board....I have an idea...maybe.

what is the code to "Lock Project for Viewing" or prevent access to a vba module? I should be able to check xL version information upon WB opening and protect module based on the version opening it. xL 5.0 allows typical '.visble=false' and '.protect password:=' assignment. If a newer version of xL is used it would apply the protection accordingly...i'll research that one. THANK-YOu for all of your help. I learned something new!!