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 Transfer of Excel Workbook

    We want to prevent the transfer of an Excel workbook from one client to another. That is, since we charged one client for the creation and coding of the workbook, we do not want that client to be able to give it to someone else.

    Any ideas?

    Thanks!!

  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,

    It some what depends on how much access you have granted to the client it was developed for in the first place. That is have you locked them out of the VBA project with a password that they don't have? If this is the case here's what I did on a project.

    I created a separate workbook that contained all the lists needed for operation of the program and any identifying information like the company name that would be displayed and printed. Of course code is used to update these lists and only allows the NON identifying information to be changed.

    Then the Auto_Open Macro for the main file will check for the existence of this file and load the appropriate information. Of course it is Excel and passwords can be cracked, as I'm sure someone would point out, but this all depends on the technical expertise of the clients.

    So for what it's worth:

    Code:
    '                         +-------------------------+             +----------+
    '-------------------------|       Auto_Open()       |-------------| 07/11/05 |
    '                         +-------------------------+             +----------+
    'Calls: [Updates]AutoUpdate
    '       [Updates]AutoUpdateLists
    '       RecordsMenu
    
    Sub Auto_Open()
    
       Dim iWkBkCntOffset    As Integer
       Dim oListsFile        As Object
       
       GoTo StartSetup
       
    NoListsFile:
    
       MsgBox "The file {Master Lists.xls} is missing!" & vbCrLf & vbCrLf & _
              "Please contact your support personnel immediately..." & _
              vbCrLf & vbCrLf & _
              "The menu will display empty...please close it using the" & _
              vbCrLf & "RED X in the upper right corner of the menu.", _
              vbCritical + vbOKOnly, _
              "Error: System file Missing"
       With Application
           .DisplayAlerts = False
           .Quit
       End With
       
    StartSetup:
       
       With Application
           .ScreenUpdating = False                    'Turn off Screen updating
           On Error GoTo NoListsFile
           Set oListsFile = Workbooks.Open("Master Lists.xls", , False, , , "ComputerMentor")
           On Error GoTo 0
           AutoUpdateLists oListsFile
           zOfflineStorage = Range("ExternalBackupPath").Value
           zLocalStorage = Range("InternalBackupPath") 'Production Backup Location
           zAgency = Range("Agency").Value
           bExtraQs = Range("ExtraQuestions").Value
           .DisplayAlerts = False
           oListsFile.Close
           .DisplayAlerts = True
           
           bExcelVer10Plus = IIf(Val(.Version) > 9, True, False)
           .CommandBars("Standard").Visible = False   'Hide Standard toolbar
           .CommandBars("Formatting").Visible = False 'Hide Formatting toolbar
           .CommandBars("Drawing").Visible = False    'Hide Drawing toolbar
           .DisplayFormulaBar = False                 'Hide Formula bar
           
           If bExcelVer10Plus Then
             .ErrorCheckingOptions.UnlockedFormulaCells = False
             .ErrorCheckingOptions.EmptyCellReferences = False
           End If
           
           HideStdMenu
       End With  'Application
       
       If Workbooks(1).Name = "PERSONAL.XLS" Then
         iWkBkCntOffset = 1
       Else
         iWkBkCntOffset = 0
       End If
       
       If Workbooks.Count = iWkBkCntOffset + 1 Then
         ufSelectFile.Show
         Set oWkBk = Workbooks.Open(zFileToOpen, , False)
       Else
         Set oWkBk = ActiveWorkbook
       End If
       
       DeleteSheets
       
    '*** Call auto update program to make necessary changes to the data files
       AutoUpdate
       
       If ActiveSheet.ProtectContents Then UserUnprotect
       Application.WindowState = xlMaximized
    '   Cells(2, 5).EntireColumn.Hidden = True  'Show/Hide the Month Column
       If Not ActiveSheet.ProtectContents Then UserProtect
       
      RecordsMenu
      
      Application.MacroOptions macro:="'VBA for Records Review.xls'!NewFY", _
          Description:="Create a file for the New Fiscal Year", _
          ShortcutKey:="N"
         
    End Sub                  'Auto_Open()
    Note: ComputerMentor is the password for the Master Lists file the VBA project is protected and hidden w/a different password.

    Of course there are other methods like creating a signon dialog which requires a UserId and Password and creating the Userids from the ID returned by the UserName = Environ$("UserName") and verifying against a list stored in your program or separate file.

    Ultimately, you can't stop a determined hacker but you'll probably be successful in stopping some who had to hire you to develop the Excel for them.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Marina del Rey, CA, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks you so much for your thoughtful answer. We didn't protect the VBA but may be able to do so and modify the code to execute a similar macro checking for something.

    Other ideas are still welcome!!

  4. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,168
    Thanks
    47
    Thanked 978 Times in 908 Posts
    For my usual alternative view, the issue of copyright is paramount here.
    Did you write the spreadsheets on the client's instructions? If so the copyright is with the client, unless you agreed otherwise, and the client can give the software to anyone they like.
    If the client holds the copyright you should not password protect the VBA, unless you provide the password to the client.

    cheers, Paul

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I'd put the client's name in prominent locations, in protected cells, and on any print formats you have provided.

    Then, if you like, you can write code in the auto-open Macro which prevents the Workbook from opening if the correct name isn't present.

    I can think of lots of ways to make that hard to bypass but maybe it's enough of a starting point for you to think about ?
    Last edited by MartinM; 2016-01-10 at 11:51.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    I would do a deal with the Client so that you shared any profits together.

    zeddy

  7. #7
    New Lounger
    Join Date
    Dec 2009
    Location
    Marina del Rey, CA, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    There are several levels. My friend's former employer owns this workbook. He developed it for them and will receive a cut on each one they sell. They install ERP systems and this workbook eases the immense effort of determining which employees can interact with which elements of the ERP system. I help my friend and get hourly pay for Excel VBA programming.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2009
    Location
    Edinburgh, UK
    Posts
    237
    Thanks
    24
    Thanked 49 Times in 21 Posts
    Do not depend on being able to password the Excel workbook. In my experience, they are depressingly easy to hack. Your remedy would appear to lie in your client agreement.

Posting Permissions

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