Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Protection with VB (Excel2000 VB6.0)

    One of the concerns many of us Excel developers have is how can we protect our applications.

    Both worksheet password protection and VBA protection are easily bypassed with code cracking mechanisms. I have put together a VB6.0 project which seems to get around this. I have coupled this with a simple spreadsheet containing 4-5 formulas and am attaching it to this post as a Beta Test version.

    Anyone interested in testing it? If you are able to access the formulas, please let me know how you are able to accomplish it.

    Thanks

    Paul
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Protection with VB (Excel2000 VB6.0)

    Do you want to know how it's accomplished publicly or privately? I have found one way so far.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Protection with VB (Excel2000 VB6.0)

    I share your concerns, but I had no trouble finding the formula, and I've never done anything like that before. (I was a little concerned that I would never see my customized toolbars again! Thanks for having VB restore them.) --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection with VB (Excel2000 VB6.0)

    Got them.

    Just unprotected the sheet and bingo.

    The first one is:

    =14+(SUM(B5:B6)^3)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection with VB (Excel2000 VB6.0)

    Rory, Sam & Jan,

    Thanks for your assisstance.
    Did you remove the protection before opening the spreadsheet or while it was open? If the spreadsheet was closed while you removed the protection, did you then open it as a standalone (from xl) or through the VB proj?

    Paul

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Protection with VB (Excel2000 VB6.0)

    While the VB project was "running" the spreadsheet, I customized the toolbars and added a SaveAs. I did a SaveAs with a different filename, then exited everything via the task manager. Then, I came and went normally which restored my toolbars. Finally, I opened my SaveAs'ed spreadsheet and disabled macros, cracked the password with Erlandsen tools, removed worksheet protection, and looked at the formula. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Protection with VB (Excel2000 VB6.0)

    In the above post, I was running Win XP/XL2K.

    I now have Win NT/XL97 up. I ran your VB project and was able to unprotect the sheet without saving it. VB got hung-up on the FindWindow line:
    <pre>Sub ShowForm3()
    'Get Windows Handle of Form
    hwnd = FindWindow(vbNullString, "Form3")
    'Now show the window
    ShowWindow hwnd, SW_MAXIMIZE

    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Protection with VB (Excel2000 VB6.0)

    Arggg! Now, you really got even. Since Excel won't close normally, my toolbars are hosed!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection with VB (Excel2000 VB6.0)

    <P ID="edit" class=small>(Edited by charlotte on 24-Feb-03 19:41. )</P>Well,, Obviously I have some more work to do.

    I would be interested in hearing how you were able to see the formulas without the "Tamper" message closing everything up.


    Thanks

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection with VB (Excel2000 VB6.0)

    XL 97 SR2:

    Opened the sheet in two ways:

    - just opened the workbook
    result: no formulas

    - with your application
    My toolbars were all still there
    used this macro to unprotect the sheet:

    Sub PasswordBreaker()
    'Breaks worksheet password protection.
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer

    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
    ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
    Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) _
    & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    If ActiveSheet.ProtectContents = False Then
    MsgBox "One usable password is " & Chr(i) & Chr(j) _
    & Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) _
    & Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    Exit Sub
    End If
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
    End Sub

    Then formulas were freely available.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection with VB (Excel2000 VB6.0)

    Sam,
    I've made some adjustments as per the attached file. Since you were able in version 1 to gain access through the customizing of toolbars, I have replaced the Standard Menu with my own CustomMenuBar.

    With this arrangement, on my machine (Win2K, xl2K) I am no longer able to access View>>Toolbars>>Customize or to Rt click a popup to do the same. Hence, I cant get access to the SaveAs (which is key to your method). Maybe this is still accessible on other configurations.

    BTW if anyone has problems restoring toolbars, use this code:

    Sub RestoreToolbars()

    Toolbars("Standard").Visible = True
    Toolbars("Formatting").Visible = True
    Toolbars("Drawing").Visible = True
    Toolbars("Visual Basic").Visible = True

    'etc

    End Sub

    Thanks
    Paul
    Attached Files Attached Files

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel Protection with VB (Excel2000 VB6.0)

    Paul,
    With your new version, I can still right-click on the toolbars and customize that way. Or go into the VBE via Alt-f11 and run a SaveAs from there. Incidentally, the way I did it originally, since your tamper message worked for me at any rate, was to open the workbook via your program, then use Task Manager to end your app, leaving Excel running. I could then do what I needed without your tamper message doing anything.
    FWIW.
    PS I just noticed that the workbook produced by the SaveAs did not have the formulae in it, unless I killed the app first.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection with VB (Excel2000 VB6.0)

    FWIW...

    As long as your application relies on Excel's protection schemes and on disabling commandbars etcetera, a power user will find a way to circumvent that simply by using code.

    I stronlgy suspect the only really reliable way to protect your stuff is NOT to use Excel <bg>.

    A good alternative is to produce a COM addin and put all relevant/critical functions in there. The user will be able to see the functions in the formulas in Excel, but will have no clue what they do.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Protection with VB (Excel2000 VB6.0)

    I'll just echo Rory's & Jan Karel's comments. I don't see how you can prevent me from cracking the VBA password and writing VBA code to SaveAs. Anything that has intellectual value has to be written as a COM AddIn. Which BTW is one of the things that I'm working on for a living. But, (and I wish I could convince my boss of this) converting a bunch of sloppy-written macros, toolbars, & userforms into a COM addin is a very difficult task. Now, to see if I can recover my toolbars! <IMG SRC=http://www.mtl.com/sam/images/mack.gif> --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  15. #15
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Protection with VB (Excel2000 VB6.0)

    Thanks guys

    Can you direct me to some good sources for learning more about COM addins?

    Also in there a way to disable "Alt F11"?
    Paul

Page 1 of 2 12 LastLast

Posting Permissions

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