Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

How to allow modification to only one person

0

Hi,

So I'm selling some excels to make a little extra and I would like to know how to allow modification to only the person I sell them to. If I give them a password they might just sell it back with the password right ? I want to make it so that if they try to share it to a 3rd person, this person won't be able to do modifications on it.

I hope I'm clear enough. Thanks already for your answers :)

Answer
Discuss

Answers

0

To attach the use of the program to a person requires user input and you end up having some thing that resembles a second password. The nearest you might get to your ambition is to use the C-drive's serial number to limit the use of your program to one machine, in fact one drive.

MsgBox CreateObject("Scripting.FileSystemObject").GetDrive("C:\").SerialNumber

This number should be globally unique or very nearly so. You want your program to take note of the drive number on the machine where it is first run. On subsequent opening it should compare the stored number with the drive number and refuse to open if the info doesn't match.

Of course, the question is where to store it. The point is that if there is no number the program will presume that it's the first use and record the actual number. Users wouldn't know this but they might stumble upon the solution. Excel has no location to store variables invisibly (like Word). Therefore you have to store it in plain sight.

You might use a CustomDocumentProperty. Say, you call it simply "Serial". While this property doesn't exist, the program presumes that it's a new installation and creates it. It assigns a number to it like -2065125496 (the number of my C-Drive). Next time the program finds the property, reads the number, compares it to the serial number on the machine where it is installed and refuses to open if there is a mismatch.

Now, if the workbook is loaded to another machine it won't open because the serial number in the property doesn't match the serial number of the drive. But all a user has to do to transfer the program for use on another PC is delete the custom property. Therefore you protect it. For example, when the workbook is opened and a "Serial" property is found you write the number to an empty cell somewhere in the workbook. You can make it a random cell and write it invisibly and even encoded. When the workbook is closed the number is retrieved from there, restored to the property and deleted from the worksheet. If the user deleted the property it was restored automatically.

With this system the verification process includes both the custom property and the hidden cell. The user has to delete both to pretend that the installation is virgin. He has to know what to delete and where to find it. It's not perfect but it erects some barriers.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login