• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Ideas for monthly license verification?

GaryC

GaryC

New Member
I have created what I think is a great set of VBA code (I haven't programmed in over 15 years, but it all seemed to come back to me). Okay, it could probably use some cleaning, but it definitely does the job. One of the things I am looking to do is add licensing. The people I made it for offered to pay me a monthly maintenance. How can I write it into the code that if they don't pay, the file won't work? I can put an updated file on a webhost for it to check monthly (if that is the suggested way). Any ideas? I appreciate everyone's help in advance.
 
.
This is one method. Understand, if the user knows how to get into the VBE they can disable this protection.

Code: 
Option Explicit

'<----PASTE THIS MACRO IN THE THISWORKBOOK MODULE

Private Sub Workbook_Open()
On Error Resume Next
Dim Edate As Date
Dim sh As Worksheet
Edate = Format("30/11/2016", "DD/MM/YYYY")  '<-- expiration date here
    If Date > Edate Then
        MsgBox ("This Workbook was valid up to " & Format(Edate, "dd-mmm-yyyy")) & vbNewLine & _
        ("It is now a Read Only Document"), vbExclamation, "Tools"
For Each sh In Worksheets
    With sh

        .Unprotect Password:="password"    '<-- password goes here
        .Cells.Locked = True
        .Range("A:XFD").Locked = True
        .Protect Password:="password"      '<-- password goes here
     
    End With
Next
ActiveWorkbook.Save

End If
End Sub
 
Gary

A web based query of a a file, database or other location is fine and will work in majority of the cases.

Just be aware that there are no foolproof ways of preventing somebody with appropriate skills to hack these solutions.

Locking files and VBA only slows down honest people
 
Thank you both for your reply. That routine is clean and right down to the point. It makes sense. I can update a text file on my website to verify that they have paid, but I just need the script to look in that file without actually downloading it. Hui, how would I do that web based query? Would I have to download a file then open it, or can I have it look in a text file on my website without it being downloaded?

As far as security, Hui, I have given that a lot of thought. I found this company: https://www.spreadsheet1.com/unviewable-vba-project-app-for-excel.html#buy

Can you give me your opinion on that product? If security on that is no good, perhaps I should just make it in Visual Basic and have it reference the spreadsheet? The purpose of the spreadsheet is that is how my client gets their reports. They are comfortable using Excel, and it is familiar to them. They do not want to lose that familiarity of Excel while still automating the process in order to avoid human errors and increase their speed. That's the reason I chose VBA for this.
 
For what its worth ... Hui is really good at breaking into workbooks. I've sent him several examples of "locked / protected" projects and he was able to break into each one. (I suspect he has some kind of magic dust he throws at the screen.)

I also tried the product you have linked to. Sad thing is, I was able to break their security measures myself. And I don't really know much about bypassing passwords.

As Hui mentioned, the built in password in the VBE and other methods will slow down the honest person ... for the devious minded, it will be an inconvenience for a second. And Hui's magic dust will devastate the security measures.

My $.02
 
  • Like
Reactions: Hui
You can easily access a web site or a remote Access DB to retrieve or check validation dates against say a user name or ID code

The VBA Security measures will deter all but the serious hackers

Another way would be to Write a value to a DB every time the file is opened or closed as well as read a validation date when it is opened

That way you have a record of when they are using it as well
 
Excel, and VBA specifically is very difficult (nigh on impossible) to solidly defend from someone looking to circumvent protections put in place as stated above.
I would suggest an option might be to place some key logic/process step (depending on the function of your creation) behind a more defensible setup.
An example of this might be to have a calculation core to the functionality of your workbook happen on request on a server, perhaps behind a password or whitelist.
Obviously this has the potential for reductions in efficiency, and requirements beyond just a workbook. Taking this idea to it's logical end would imply that most/all your functionality should occur outside the workbook, which is again not necessarily an attractive proposition.
Just thought i'd give my 2 cents on another option, perhaps not a viable one in this scenario, but an option nonetheless.

Stevie
 
Thank you Logit, Hui, and Stevie. I appreciate all of your help. I read a lot of posts that Hui was in before I posted this. I learned a few tricks from his other posts. His profile says "Excel Ninja," and I believe it.

Full story I first got this script set up for my business. I wasn't worried about security or anything fancy because it was just for me. That, and while I started programming with Pascal, Basic, COBOL (a little bit) and the the beginning of HTML and the first release of Visual Basic, I haven't programmed at all since the early 2000's. Life took me in a different direction. Anyway, I dug into my own history, and looked up a lot of new "stuff" to make this script for me. When I talked about it, someone in my same field wanted to use it. Since it's new, and I'm no pro, I figured a monthly thing would be better than asking for something real as a 1 time payment, but no matter how I do it, I don't want my sweat and tears being easily taken by someone else.

As Hui pointed out, only the most serious hackers will get through anyway, and this program isn't one of these high level programs anyway. Plus this field of people isn't known to be filled with computer geeks (like me). Not impossible, but not likely.

Hui, you mentioned writing a value to a DB when the file is opened and check the validation date. I like that idea a lot. I know how to pull data from a website and put data on one, but how do I open a file without downloading it locally? If I were to have it write to an Excel or even a txt file on my website, how would it open it without downloading it? Or would it have to download locally? I'm thinking if it downloads, then someone can see in it and keep it local as a simple way to bypass my attempt at security.
 
Here is my XML file (of course it will be much longer, but this covers it). My goal is to update the XML file when the customer pays. Notice how the file says "August." Come September, the program will not run because the code I enter will not match the September code in the VBA file. Once they pay, I'll update the code in the XML file, then when they run the file, it will work.

I can get it to put the entire XML file in a message box, but I do not know how to search for the agency name then grab the month and code to verify. I think I want the VBA macro to read the XML file, search for Agency Name 2 (for example), when it finds Agency Name 2, grab the date and code. Verify that this date and code match what is expected in the VBA file, then it will allow the rest of the subs to run. Concurrently, I'd like it to put a date, time, and some other stuff back in the XML file as a way to see when they used it last.

I know this isn't great security, since if they can just get past a password they can change the script to avoid this, but it is something. Should I scrap all this and just do it in Visual Basic?

Below is the XML, and below that is my sub to get me a msgbox of what is inside the XML, but I have no idea how to search or write what I am thinking about above. Please give me some advice.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<data-set xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<record>
<Agency>Agency Name 1</Agency>
<Date>August</Date>
<Code>code to give</Code>
</record>
<record>
<Agency>Agency Name 2</Agency>
<Date>August</Date>
<Code>code to give</Code>
</record>
</data-set>

-----------------------------------

Dim xmlhttp As Object
Dim myUrl As String

Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")

myUrl = "https://www.mysite.com/myfile.xml"
xmlhttp.Open "Get", myUrl, False
xmlhttp.send

MsgBox (xmlhttp.responsetext)
 
You must log in or register to reply here.
Back
Top