• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 338
  • Last Modified:

Securing Macro from tampering


I've a very specific requirement. I'm going to create an excel workbook on the fly (thru program) based on a template. Now, there'll be two set of users, one will have permissions on certain cells whereas the other will have permissions on different set of cells. Also I've certain validations to do while users enter data for which I need to write VBA code.

Now, when the file arrives from a first user, I've to determine (thru the code itself) that user has filled up those very cells only on which it was permitted. Also I need to check that the VBA code that I had in workbook has not been deleted or tampered with.

Can somebody help plz., I'm in dire straits

1 Solution
I might have got it wrong, but just some thoughts:

On startup create a form that askes for a password. Depending on which password is given (one for each of the two kind of users) unlock the cells the user is permitted to. (Preferably by naming the two different sets of cells and unlocking/locking the named area) It will then be impossible for the user to fill up any other cells.

The code can be protected for viewing, and thereby also editing. Choose Tools - Preferences and Protection. State a password. The code will however not be invisible for view until the next time the file is opened.

Regarding the validation I do not really get what kind of help you are after..

Now, did that help you?

If I got ntdude2k right, he's asking how to protect the code of a workbook generated on the fly. So you can't go to the menus and protect it because he wants some automated form of doing it. (I may have got it wrong though).

If this is the case then I think that you can't do it. There is the Protection property of the VBProject that locks the code but this will only prevent the users from getting the Debug button. Unless you specify a password the code will still be available at the VBA editor. And this password protection is what can't be done.

(Maybe by using SendKeys you could simulate the behaviour but I don't know if it would work. I think I saw some code that implemented this recently... I'll take a look around and post it here if I can find it).

Dave BrettVice President - Business EvaluationCommented:
Hey Paulo

I had a play with this the other day for Tony 813. This one protects and I've managed to get it to unlock as well.



Sub vba_protect()
'must be outside VBE as first command switches
    SendKeys "%{F11}"
    SendKeys "%te"
    SendKeys "^{PGUP}"
    SendKeys "{TAB}"
    SendKeys "aaaa"         ' password
    SendKeys "{TAB}"
    SendKeys "aaaa"         ' password confirm
    SendKeys "{TAB}"
    SendKeys "{ENTER}"
End Sub

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Hi brettdj. That was the question I was referring to. Glad you could join here :)

ntdude2kAuthor Commented:
Thanks everyone for an overwhelming response. This shows that I'm not alone here :)

Hey Paulo,

You got me right. It is sad to know that it's not possible thru Automation. (I'll tell you why I can't use SendKeys)

I would also require some suggestions from you guys - whether I can add/read some code in an Excel Workbook (created on fly) thru Automation - (sounds like some Virus right....  :)  )

I'll tell you the exact scenario I'm planning for --

A user (of a particular category)  visits my WEBSITE, asks for a file to download so that she can fill it up  offline and then again upload. For this, the web-page invokes an application on the server that creates this Excel file, and shows the user for downloading (plz note that since its a web page, everything happens without the user intervention).

The first user downloads it, makes some changes (in the relevent sections only) and uploads it again. Now, a second user (of different category) needs to download the very same file and fill (only) sections relevent to that category of user.

Thereafter, this file again is submitted back to my site, and I need to read thru the values and store it in my DB.

At all the time, I want that whatever code exists in my (created ) excel file, users should not be able to edit those. And I've to ensure that it is the very same excel file that was created by my program (to avoid any copy - paste trick by the two categories of users).

For the SendKeys, I don't think its too reliable to use in an enterprize application. You never know, to which application it starts sending the keys (geeeee... never know what may happen thereafter ;) ).

Thanks a ton,
Here's a couple of snippets that add code on the fly to a newly built workbook. They have been directly copied from a document of mine so they don't as is work. But should be enough to get you started.

--            Set btn = wsReport.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
                Link:=False, DisplayAsIcon:=False, _
                Left:=wsReport.Cells(1, 8).Left + 5, Top:=wsReport.Cells(intPos + intCounter, 1).Top + 2, _
                Width:=10, Height:=10)
            btn.Placement = xlMove
            With wbReport.VBProject.VBComponents(wsReport.CodeName).CodeModule
               .AddFromString vbCrLf & "Sub " & btn.Name & "_Change() " & vbCrLf & _
                            "   On Error Goto BailOut " & vbCrLf & _
                            "   AddToChart """ & rngLTRow.Cells(1, 1).Value & """, " & btn.Name & ".Value, ThisWorkbook.Worksheets(""AuxiliarLT""), ""tableMeasuresLT"", ThisWorkbook.Worksheets(""Report"").ChartObjects(""LTChart"") " & vbCrLf & _
                            "   Exit Sub " & vbCrLf & _
                            "BailOut: " & _
                            "   MsgBox """ & strReplace(Translate(336), """", """""") & """ " & vbCrLf & _
                            "End Sub" & vbCrLf
            End With
            'Add the code of the AddToChart Function
            wbNew.VBProject.VBComponents.Import (ThisWorkbook.Path & "\AddToChart.bas")
            wsReport.Protect ThisWorkbook.Password

The first one adds a checkbox to a sheet and the code to handle the Click event for it. The second one adds a module to the workbook.

You basically need to get the VBProject (one of the workbook's properties) and handle the VBComponents inside it. Take a look at the help for reference on these objects or ask here for specific questions.

Hope this helps

ntdude2kAuthor Commented:
Hey Paul,

For that "Code Protection thru Automation" thing, it seems as if its really not feasible. I might have to look for some alternative.

Thanks a lot!!! On your suggestion, I got quite a few resources for Updating code on the fly.

Cheers  :))
If you find a way to protect the project don't forget to come back and tell us. As you see you're not alone.

Thanks for the points. I'm just sorry you couldn't get the answer you were looking for.


Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now