I am looking for a way of protecting the workbooks with VBA code. The case is: the student downloads a spreadsheet from a website (WooCommerce) and is able to run it on only one computer with no possibility to send it to other students.
My idea of solving this (pseudo-code):
Private Sub Workbook_Open()
-- check if the workbook is opened for the first time (eg. if the chosen cell is filled with data)
--- if it is empty then prompt: "Would you like to bind this spreadsheet with this computer?" Button:YES/NO
---- If NO then close the workbook
---- if YES then read the computer specs and write it as hash in chosen cell
--- if the cell is filled with data check if computer specs match the hash
---- if they match run the workbook
---- if they vary close the workbook
End Sub
This idea does not solve the problem of sharing the workbook before opening – do you have any ideas how to make it with WooCommerce?
I would appreciate if there is somebody who could help me write proper vba code 🙂
Below I paste the solution for reading the computer specs I found on mrexcel forum, it might be helpful:
Option Explicit
Public ProcNum As String
Sub ProcessorNumber()
With Application
.ScreenUpdating = False
.DisplayAlerts = False
.EnableEvents = False
Dim WMI As Object, WQL As String, Proc As Object, Procs As Object, i As Integer
Sheets.Add
i = 1
Set WMI = GetObject("winmgmts:")
WQL = "select * from win32_processor"
Set Procs = WMI.ExecQuery(WQL)
For Each Proc In Procs
Cells(i, 1).Value = Proc.getObjectText_
i = i + 1
Next Proc
Set WMI = Nothing
Set Procs = Nothing
With Range("B1")
.Formula = "=CLEAN(MID(RC1,SEARCH(""{"",RC1)+1,SEARCH(""}"",RC1)-SEARCH(""{"",RC1)-1))"
.Value = .Value
End With
Range("B1").TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:=";", FieldInfo:=Array( _
Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _
Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), _
Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1), _
Array(25, 1), Array(26, 1), Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), _
Array(31, 1), Array(32, 1), Array(33, 1))
Range("B1:AG1").Copy
Range("B2").PasteSpecial Paste:=xlPasteAll, Transpose:=True
Application.CutCopyMode = False
Columns(1).Delete
Rows(1).Delete
Columns(1).TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, Other:=True, OtherChar:="=", FieldInfo:=Array( _
Array(1, 1), Array(2, 1))
ProcNum = .Trim(.Substitute(Range("B20").Value, Chr(34), ""))
ActiveSheet.Delete
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
Function DriveSerialNumber() As String
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
DriveSerialNumber = Format(CDbl(FSO.Drives("C:").SerialNumber))
End Function
Sub CompSpex()
Run "ProcessorNumber"
MsgBox "Processor number is: " & ProcNum & vbCrLf & "Hard drive serial number is: " & DriveSerialNumber
End Sub
3
Answers
The general pattern with ‘fingerprint’ protection is that the purchaser generates the fingerprint up front, passes it to the vendor, and then the vendor personalises and protects the content at their end before distributing. In the case of Excel, the content must default to hidden unless the personalised ‘unhide’ macro is allowed to run successfully.
Another option is to make your students use a USB license dongle – you must decide if that is feasible from a commercial point of view.
XLS Padlock (https://www.xlspadlock.com/) appear to specialise in exactly your problem of locking Excel workbooks and integating with WooCommerce, although I have no personal experience of them. This is likely to be a quicker and more successful route if you are new to programming – security is notoriously complex to get right even for experienced programmers.
There is no VBA way to pervent this because the student can open the “locked” file no his computer save it as
xlsx
which stripps of the macros completely (xlsx
cannot contain macros) and then send it to another user.Also he could just copy the sheets into a new workbook that is not protected.
There is no way to prevent that. Who ever can access the file can copy the file (or its data).
so im seraching for solution to berforming this operation and yet still has no good way >>>>>
but the only solution that work with me in past to insure that you done all codes in vba project and set autmatic calculation by userform activat or by bottun ,then
use donexle or xl compiler to great an completely protected ex4e virion that come with hidden VBA code and user has no access or ability to save the file except in tembrory file with exe extinction too <
the fun part you can set the duration that they can use the program and great license key assosiat with any serial you get from your Clint PC and many other great option
remmber just write this in open workbook
application workbook.hidden= true
userform 1.show
to hide main excel for more professionally done the jop the choose your main form that run the whole application test it and try several software that do the same main jop