Securing Macro from tampering

Posted on 2003-03-12
Medium Priority
Last Modified: 2012-08-14

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

Question by:ntdude2k
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 8118886
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?


Expert Comment

ID: 8119133
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).

LVL 50

Expert Comment

by:Dave Brett
ID: 8119267
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.


Expert Comment

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


Author Comment

ID: 8119846
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,

Accepted Solution

pauloaguia earned 1000 total points
ID: 8120009
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


Author Comment

ID: 8127432
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  :))

Expert Comment

ID: 8127572
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

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question