Excel protection: allow specified people to use/modify macro, but prevent them to change protection

[gifariz]

This question is a bit complicated. My ultimate goal is to prevent other companies from using our macro-excel, but employees in my company can see/modify the macro. This includes preventing employees in my company to remove/change the protection system.

So far I can allow specified people to use my macro by checking their IP address, see if it match with listed IP address in the code (detail in here).
Those people needs to be able to see/modify the other modules in the file, except the protection subs in ThisWorkbook.
I wonder if we can put password only to the "ThisWorkbook" excel object? It seems that it is not possible.

Any suggestion regarding this? Other methods are okay to achieve same goal.
Thank you in advance.

 

[sijpie]

OK, first for the bad news: anybody with a bit of expertise can crack passwords, both Excel passwords (easy) and with some more work VBA passwords.

If you don't want your client to see or use the VBA, then save the workbook as an .xlsx before you send it to them.

That will be the easiest option.

Then option 2: if you trust that password protected VBA is sufficiently safe:
At the start of any macro that only employees can run, check the IP address and exit sub with an error message if wrong IP address (How about co-workers working from home?)

But like I said earlier, this system can be broken

If very important, then you could work with a compiled add-in. This could be written in VB, C# or similar and compiled. Then the important macros can be held in this add-in, with no chance of kidding the system. But working with add-ins has its own pain...

 

[Candyman8019]

Along with the caveats that sijpie mentioned above, you could add to all of your macros/vba to check the domain of the computer and if it matches your company's domain then continue, else end sub. Something similar to:

Private Sub domaintest()
Set wshNetwork = CreateObject("WScript.Network")
If wshNetwork.UserDomain = "MyCompanyDomain" Then
' macro details here
Else
End If

End Sub

 

[gifariz]

OK, first for the bad news: anybody with a bit of expertise can crack passwords, both Excel passwords (easy) and with some more work VBA passwords.

If you don't want your client to see or use the VBA, then save the workbook as an .xlsx before you send it to them.

That will be the easiest option.

Then option 2: if you trust that password protected VBA is sufficiently safe:
At the start of any macro that only employees can run, check the IP address and exit sub with an error message if wrong IP address (How about co-workers working from home?)

But like I said earlier, this system can be broken

If very important, then you could work with a compiled add-in. This could be written in VB, C# or similar and compiled. Then the important macros can be held in this add-in, with no chance of kidding the system. But working with add-ins has its own pain...

Thank you. Sorry for super-late reply. I thought no one would reply after some days of no reply in my thread.

This is actually helpful sir. I didn't think of add-in as alternative. Thank you. I will dig more into it.
Btw, in addition of checking IP address or desktop ID, the license key is actually encrypted. Hopefully it adds more complexity to the hacker.