3

I have an file which saves a copy of itself to go to certain recipients, so you end up with many files that only contain recipient-specific information and the original maste file that contains all information. When the recipient-specific files are made, I have code that deletes everything but information that is related to that recipient and locks down the workbook and sheets with a randomly made password using the below function:

Function Pwd(iLength As Integer) As String
Dim i As Integer, iTemp As Integer, bOK As Boolean, strTemp As String
'48-57 = 0 To 9, 65-90 = A To Z, 97-122 = a To z
'amend For other characters If required
For i = 1 To iLength
    Do
        iTemp = Int((122 - 48 + 1) * Rnd + 48)
        Select Case iTemp
        Case 48 To 57, 65 To 90, 97 To 122: bOK = True
        Case Else: bOK = False
        End Select
    Loop Until bOK = True
    bOK = False
    strTemp = strTemp & Chr(iTemp)
Next i
Pwd = strTemp
End Function

Is it possible to also lock down the modules so that they cannot be edited? What I want is the same functionality that Excel provide in Visual Basic by going to Tools -> VBAProject - Project Properties -> Protection, but to do this through the code so that it can be applied to each of the recipient specific files.

I can apply protection to the sheets using code like:

Sheets(1).Protect Password, True, True

And to the workbook with code like: 

ActiveWorkbook.Protect Password, True, False

But is there something that I can use to lock down the modules?

Ben Smith
  • 809
  • 5
  • 21
  • 46
  • Excel passwords are not secure; it is trivially easy to break them if the file is on your computer. If the data being sent to all individuals is truly sensitive, you need another delivery method. For example, have Excel create the files (while under ownership of you or someone with authority to see all the sensitive data), and drop them into network folders which only you & the particular owner has access to. – Grade 'Eh' Bacon Jan 27 '16 at 13:24
  • If on the other hand this is more of a convenience thing (no reason to bog down a salesperson with IT manuals on a combined project, or something like that), then an Excel password would be fine. For example, when using template files where the background calculations are all automated and would break if a user interfered, I will lock down those sheets. While the users would still have access to those sheets if they bothered to break the password, having that as an extra step is a pretty effective way of saying 'you have stepped outside of the limits of what I will support'. – Grade 'Eh' Bacon Jan 27 '16 at 13:26
  • @Grade'Eh'Bacon: a slight nuance: the file password is actually secure since Office 2007, but the rest indeed is not. – Carl Colijn Jan 27 '16 at 13:32
  • 1
    The only way to manipulate the VBA password is to cheat and use SendKeys. There's no API for it that is exposed by Excel (or any other Office app for that matter). See: http://stackoverflow.com/questions/16174469/unprotect-vbproject-from-vb-code – Carl Colijn Jan 27 '16 at 13:34
  • I understand that Excel's protection isn't the most secure, however the idea is that even if a user hacks the protection they will not be able to see anything but their own data, i.e. what the code does is it saves a copy of the master file with all the data in, goes into each data sheet and deletes everything that is not appropriate to the recipient, hides all the sheets except what we want them to see, then locks the sheets and workbook down - so if they do hack in they won't be able to find anything. But what I want to do is prevent them from looking at the code behind the files... – Ben Smith Jan 27 '16 at 13:39
  • ... if they see the code then it isn't the end of the world because there's nothing damaging in there to see, but for the sake of consistency and tidiness it would be good to password protect the code from being viewed – Ben Smith Jan 27 '16 at 13:40
  • @BenSmith Instead of pulling the whole sheet and then deleting the data meant for other users, why don't you just pull the single tab relevant to that user? Also, if a user runs Excel and it has access to a location on the network storing the master sheet, then isn't that file unsecured, meaning they could just go to the folder and grab it? – Grade 'Eh' Bacon Jan 27 '16 at 13:44
  • @Grade'Eh'Bacon The reason for not just pulling the single tab is because there are two main tabs that we want the recipient to be able to look at, and on these tabs they can change certain measurements, such as time period, that would change the data they see - so the data sheets are still necessary. And these files will be emailed out, as opposoed to on a shared network, so they will not be able to get to the master copy. – Ben Smith Jan 27 '16 at 13:50
  • 1
    @BenSmith I don't get it. You are basically creating new files with relevant editable data to the client where everything else has been erased. So you'll have to somehow merge the data the recipient sends back to you into your master file. In that scenario, what is keeping you from simply making the master file generate new code free files with all relevant tabs of any given recipient and sending that file instead of an edited copy of the master file with vba code you don't want to show? – InBetween Jan 27 '16 at 13:54
  • @InBetween sorry I don't understand where you are getting your scenarios from, the recipients do not send anything back, I do not update the master file with anything that they send back - these files are created and sent to the recipients so that they can view their own appropriate information, they do not send anything back - if they did and they needed to edit the files then there would be no point in locking the files down. – Ben Smith Jan 27 '16 at 14:00
  • 1
    @BenSmith Even better. I still dont understand why you need to send a pruned master file? Can't you have a blank template .xls file with all the necessary structure (sheets, charts, etc.) and strictly required code for viewing purposes already conveniently protected? Any time you need to send data to a recipient, make a copy of the template, copy only relevant data to it and send that file. – InBetween Jan 27 '16 at 14:24
  • @InBetween copying relevant data to the recipients would be too time consuming, there are many sheets within this file and many recipients, it would take me many hours to copy the relevant data into a blank template for each recipient. Besides, this is not what I'm asking in the question, I need a way to lock down the code using VBA so that a user has to enter a password to view it. – Ben Smith Jan 27 '16 at 14:31
  • 1
    @BenSmith You'd obviously need to have the necessary code in the master file to create the new files automatically, I can't imagine it would be too hard to code what is basically copies, pastes and save as operations, and I very much doubt that would be significantly slower than pruning the master file once for every recipient. And yes I know you are asking something else, but what you are asking is not easily done, I'm not even surre its possible at all without some extreme hacks, so its always smart to look for workarounds. – InBetween Jan 27 '16 at 14:34
  • @InBetween the code to run probably takes about an hour, if I were to do this manually (and this is disregarding any human errors that could be made) it would take me around 3 hours, so you're suggestion isn't even a workaround and, whilst I appreciate your effort, does not help me with what I need to do. – Ben Smith Jan 27 '16 at 14:45
  • 1
    Does the code actually have to be in the files you're distributing? Why not have the master file that you're customizing have NO code? Store the code in a different workbook, loaded as an Add-in, perhaps, and run it on the master file... – Cindy Meister Jan 27 '16 at 17:10
  • Does this answer your question? [Best way to protect Excel VBA code?](https://stackoverflow.com/questions/16757119/best-way-to-protect-excel-vba-code) – TylerH Feb 01 '21 at 19:10

1 Answers1

4

Notwithstanding the good advice already given about the security of Excel passwords/protection, and the comprehensive (non-SendKeys) solution linked by Carl Colijn, I have used the dirty SendKeys method myself with some success - see example below, and here for more details on SendKeys. YMMV etc.

Note you will have to find the option "Trust access to the VBA project object model" in the Excel Trust Center > Macro Settings, or equivalent for your version of Excel

Sub UnprotectVBProj(ByRef WB As Workbook, ByVal Pwd As String)

    Dim vbProj As Object

    Set vbProj = WB.VBProject

    If vbProj.Protection <> 1 Then Exit Sub ' already unprotected

    Set Application.VBE.ActiveVBProject = vbProj

    SendKeys "%TE" & Pwd & "~~"

End Sub

Sub ProtectVBProj(ByRef WB As Workbook, ByVal Pwd As String)

    Dim vbProj As Object

    Set vbProj = WB.VBProject

    If vbProj.Protection = 1 Then Exit Sub ' already protected

    Set Application.VBE.ActiveVBProject = vbProj

    SendKeys "%TE+{TAB}{RIGHT}%V%P" & Pwd & "%C" & Pwd & "{TAB}{ENTER}"

End Sub
Andy Bethell
  • 56
  • 4