Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Marina del Rey, CA, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prevent Workbook From Opening (VBA)

    This is a follow-up to my January 9th message Prevent Transfer of Excel Workbook to which we received a very helpful reply from RetiredGeek to get us off on the right foot (thanks again)!

    We have a workbook that we don't want given or sold to unauthorized users. Each time anyone attempts to open the workbook, our code will check an online SQL table to make sure certain environmental variables (and company name) are equal to the approved values. If that test fails, we want the workbook first to display a message to the user and then NOT to open. Naturally, we don't want the user to be able to hit a key combination (such as Ctrl-Break) to interrupt the macro so that the workbook can be used.

    I've tried putting the code in the On Open event, but when my dialog box (using msgbox) displayed to inform the user that the security check failed, I was able to Ctrl-Break out of that code and right into Excel's debug/end dialog box and then into the very spreadsheet whose access was to be prohibited.

    If it matters, I tested this before locking the VBA code as RetiredGeek had suggested. I will do so after finishing the code.

    If more details are required, please ask. Someone else is writing the code to check the online SQL table for approved values. My preliminary code worked as planned (workbook never opened) as long as I didn't try to Ctrl-Break out of it. The user who hired us hopes to sell many copies of this workbook and wants to ensure that only authorized users can access it regardless of who obtains a copy of it.

    If there are any other "gotchas", I'd appreciate being told about them.

    Thanks as always!!!!
    Mark

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Mark,

    For new readers here's a link to the original thread.

    You'll find that Locking the VBA project is essential.

    Another method you can employ is to set all sheets but one to the value xlveryhidden. Doing this makes the sheets unable to be unhidden except via macro or the VBAProject (which you are going to hide).

    The one sheet you don't hide is a blank sheet (I even turn off the grid lines) and it displays a your pre-authorization messages and failure messages. If the user is authorized then the macro sets the xlveryhidden sheets to visible and hides the blank sheet.

    Of course you will have a on Workbook_BeforeClose event that will reset all the sheets to xlveryhidden and redisplays the blank sheet.

    Breaking out of the Auto_Open macro will leave the user with a blank sheet and no indication that there are other sheets in the workbook and no way to discover them, obviating them cracking the VBAProject password (which ain't all that hard so use a long one!).

    Here's a little piece of code I use a lot which disables the Close Box (Red Box white X) in the upper right corner of Excel. This forces the user to use your menus/buttons to close the workbook and would be a good place to put your code to hide the worksheets at exit time. BTW you'll notice code to check the excel version as there are different things to check for in 2003 or before and 2007 and after.

    Code:
    Option Explicit
    
    ' Programmed by: Computer Mentor aka RetiredGeek @ WSL
    ' Date         : 22 Jul 2005    Version: 1.0
    ' Updated      : 03 Sep 2014    Version: 2.0
    ' Copyright    :  2004 Computer Mentor (feel free to use despite this notice)
    
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    
       Dim bDispMsg        As Boolean
       Dim bExcelVer10Plus As Boolean
        
       bDispMsg = False
       
       With Application
       
           bExcelVer10Plus = IIf(Val(Application.Version) > 9, True, False)
    
           If bExcelVer10Plus And .CommandBars("Worksheet Menu Bar").Enabled Then
             bDispMsg = True
           Else
             If Not .CommandBars("Worksheet Menu Bar").Enabled Then
               bDispMsg = True
             End If
           End If
           
           If bDispMsg Then
             MsgBox "Please use the Menu's to Exit the System" & vbCrLf & _
                    "Closing using the X will cause serious" & vbCrLf & _
                    "system problems!", vbOKOnly + vbCritical, _
                    "Error: Improper Attempt to Exit System"
             Cancel = True
           End If
           
       End With
       
    End Sub                  'Workbook_BeforeClose(Cancel As Boolean)
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Cross-posted at: http://www.mrexcel.com/forum/excel-q...lications.html

    Please read our policy on Cross-Posting in rule #14: http://windowssecrets.com/forums/faq...n#crossposting
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Mark

    RG offers good advice.
    My method would use two workbooks.
    The first workbook is a small Excel 'trigger' file, that has protected code that checks the authorisation requirements.
    If it passes, then the 'trigger' file will then open the 'real' workbook, by supplying a complex (hidden) 'file-open' password etc etc., and closing the 'trigger' file.
    Note, the 'real' excel file can be 'camouflaged', e.g. as a xxxxxxx.mp3 file (which means it won't open by double-clicking it in windows explorer etc etc).
    The 'hidden' file-open password is not directly entered in the vba code, rather the 'components' of it are 'retrieved' from various 'very hidden' sheets, Excel's hidden name space, and formula manipulation etc etc.

    zeddy

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Hi Mark,

    Another method that is worth exploring is to run code when the workbook opens that sends a software token behind the scenes to a hard coded email address or to send in a text message. The sent email is then permanently deleted form the folder using the MailItem.Delete method. The token is a random generated password(s) that must be entered into a waiting form with passwords fields on top of a splash screen. If I recall, one of your issues was to prevent passwords from being passed to non-registered users thus allowing work on an unregistered copy. The token will circumvent that. The rest of the sheets are xlHidden and only become visible by code ran after the correct password(s) are entered. This stops the user from sidestepping macros at startup and begin working within the workbook.

    One caveat is that the user must have Outlook installed on their computer although there are other means to sending email. You can see the latter part of the code in action here

    HTH,
    Maud
    Last edited by Maudibe; 2016-01-27 at 18:19.

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    Or just retrieve a key generated on a web page rather than email.

    cheers, Paul

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Interesting variation Paul. Are you thinking a static web page or would a dynamic link need to be emailed to the requester?

  8. #8
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    If you use a serial number you can hard code a specific web address plus serial number and use a static page on the server. Then the process is automated. Removing the web page after validation would prevent further authorization.
    If the spreadsheet is moved (different machine name, user etc), re-validation is attempted, but no response from the web site would pop up an error message.

    cheers, Paul

Posting Permissions

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