Consulting

Results 1 to 7 of 7

Thread: Locking Excel 5.0 VBA

  1. #1
    VBAX Regular Trombre's Avatar
    Joined
    Apr 2008
    Location
    Orlando, FL
    Posts
    6
    Location

    Exclamation 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!

  2. #2
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.

  3. #3
    VBAX Regular Trombre's Avatar
    Joined
    Apr 2008
    Location
    Orlando, FL
    Posts
    6
    Location
    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?

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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]

  5. #5
    VBAX Regular Trombre's Avatar
    Joined
    Apr 2008
    Location
    Orlando, FL
    Posts
    6
    Location
    Fantastic! My last question, do you know if xlSheetVeryHidden is xL 5.0 compatible?

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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]

  7. #7
    VBAX Regular Trombre's Avatar
    Joined
    Apr 2008
    Location
    Orlando, FL
    Posts
    6
    Location
    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!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •