Solved: Only you and no one else

[ndendrinos]

Hello Charlize and thanks for your contribution.
I have looked at all the kind help you people have offered to my posting and still I keep coming back to my initial question about introducing the "path" to the file without splitting the file using half as front end and the other half as back end. The reason is this : The user could copy the file before opening it thus overcoming all obstacles possible with the scenarios here. The only way to achieve my goal (that I know of) is using the path of the user's computer and password protecting the VBA.
I do not give up yet and the posting remains "unsolved" but right now I cannot think of a solution.
I thank you all

[johnske]

...The only way to achieve my goal (that I know of) is using the path of the user's computer and password protecting the VBA...

That's pretty weak protection, but...[vba]
Option Explicit
'
Sub RequirePathExample()
'requires workbook be in a folder named (E.G.) 'Gobbledegook'
With ThisWorkbook
If Not .FullName = "C:\WINDOWS\Desktop\Gobbledegook\" & .Name Then
With Application
.DisplayAlerts = False
.Quit
End With
End If
End With
End Sub
[/vba]
It sounds like you're chasing the 'Holy Grail' of VBA - making a workbook completely "Secure". This can never be, the best you can do is offer varying levels of protection (complete security is impossible).

There are a number of ways of 'upping the ante' with workbook protection - some are listed below, but it sounds like you really should be looking at the last item rather than the third last (example above). Let us know...

Protection by subterfuge:
e.g. A large splash panel is presented that requests a password be entered within a small given time. The password is non-existent, it's a decoy, what is actually required is that a very small invisible object hidden in a pre-defined spot on the splash panel must be clicked.

Password protection:
This requires the password entered to be compared against another saved:

• In a code pane in the VBE window
• In a cell on a hidden worksheet
• As a builtin or custom document property
• In another document e.g. in another .xls, .doc, .text, .bas, .frm, .frx, (etc.) document
• In the registry

Protection by a using a defined path:
Workbook must always be opened in a specially named folder in a given path

Protection by confirming identity of the person opening the file:
Username

Protection by confirming identity of the computer on which the file is opened:
Computer name

[Charlize]

Use the configuration tab that is xlveryhidden. In A1 put the date until you may run the workbook for the first time. In A2 you specify a time (15:00). I mean, when I send you the workbook now, I specify 07/11/2006 (dd/mm/yyyy) and time is 14:00 u. In Belgium time it means that you've got 5 minutes (send over at 13:55 u.) to do the first initial opening of the workbook before it becomes useless. Before 5 minutes the sheet configuration is deleted and a file is written to somewhere (maybe system directory). When he makes a copy of the file he is using, the logfile wouldn't be there and if he sends the original the time and date are passed for the first initial installation. If he is in a hurry and installs, sends the workbook to a friend and this friend installs it within the five minutes, theres nothing we can do about this. Don't send it, is another option.

Charlize

[ndendrinos]

John thank you ... not sure I understand your observation ... if I use the computer name, by asking for it I give away the solution. This is if you are talking of the computer name found by right clicking "my computer" .. Properties ... Computer name etc (on windows XP Prof)
you can change the computer name thus if i were to copy the file and give it tou you and you were to change your computer name to equal mine then you could use the copy.
On the other hand the example you give of the file in a folder on the desktop is foolproof ... if fail to see why you refer to it as "weak"... what am I missing here ?

Subterfuge I use on my "private" file were if in 5 seconds you do not enter the right word in the right cell the file self destruct ... drastic maybe but hey I said "private"



Charlize ... Quote: Don't send it, is another option. ... that one beats them all !
BUT your idea has a lot of merit in the sense that there will be no need to ask anything from the intended recipient of the file.

I still like the one of the file in a folder and I think I found a way to make it work without giving away anything ...
I will send the file using my own path ... the file will not open of course ... the recipient will email his problem ... I will ask him to then right click on the folder and give me the path ... I will then "fix" the problem and send him the "repaired" file ....
LOL just thinking how my mind works ....

Your final comments please.
And thank you again

[johnske]

I use windows 98 and the standard path for the desktop is C:\Windows\Desktop. There may be a few hundred million others still using win98, so that part is hardly unique.

By introducing a specially named folder there is some degree of uniqueness, but all that's needed is the name of the folder for a few hundred million others to run it - and you have to tell the other person that they must have this folder, so that information can be passed on.

Computer ID is a unique identifier for each computer that requires you to use an API to get the computers ID . You don't ask for it like a password, a Workbook_Open procedure could check that it's the right computer (but you would need to know your friends computer ID to write it into the procedure in the first place)

But hey, if you're happy with what you've got - run with it...

[ndendrinos]

Hello again John ... was happy now I'm not anymore ... with the info you give on Windows 98 I see the "weakness" now ... you mention Computer ID this time and not Computer Name ... not sure I know the difference if any ... on Xp Pro the name is easy to find AND change ... would you know where is the ID number for my computer stored ? If this ID is unique and cannot be changed then all I would have to do is ask for it ... not a problem.
Of course if giving out the computer's ID can lead to security issues then that's a different
situation.
Thanks
Nick

[johnske]

Well I wasn't entirely accurate there, it should probably read "fairly unique" - and yes ID and name are the same and can be changed. I was actually thinking of the installation CD serial number, but I see now it can be changed also.

Perhaps using the serial number of the hard drive would be better? i.e.
[vba] Option Explicit
'
Sub DriveSerialNumber()
MsgBox CreateObject("Scripting.FileSystemObject").GetDrive("C:\").SerialNumber
End Sub[/vba]

[ndendrinos]

Tested the code you just posted and got a 9 digit number ... so far so good
Busting my chops now trying to come up with something like:

With ThisWorkbook
If Not .Drive("C:\").SerialNumber = 123456789 Then
With Application
.DisplayAlerts = False
.Quit
End With
End If
End With
End Sub

[johnske]

Mine is a ten digit number, and running this on my machine leaves the workbook open - but running it on anyone elses machine should make it quit [vba]
Option Explicit

Sub DriveSerialNumber()
If Not CreateObject("Scripting.FileSystemObject") _
.GetDrive("C:\").SerialNumber = 1091670173 Then
With Application
.DisplayAlerts = False
.Quit
End With
End If
End Sub[/vba]The only thing is to get this number secreted somewhere the 1st time it's opened by your friend, and then checked on subsequent openings - not difficult, just tricky, and needs some thought

[ndendrinos]

Looking good over here too.
With my number the file stayed put , with your's it closed.
I think I will stop here and try finding the serial number of my disk without using your macro ... not so easy on Xp ... not sure if it's easy on W98.

John, A great thank you again for all the effort you've put in
Charlize ... I will give your solution a go tomorrow

WHOA !!! just read below your last reply: The only thing is to get this number secreted somewhere the 1st time it's opened by your friend, and then checked on subsequent openings - not difficult, just tricky, and needs some thought

You mean this is doable ??? If yes then I'm out of the woods ... no need to ask the recipient anything ....

[johnske]

Question: Is this going to only a few people, or is it going to a possibly unknown amount of ppl?

Yes, it's doable, but if it's going to an undisclosed number I foresee holes that could be exploited. But if it's only going to a manageable number of ppl there's this idea that would be more secure... you could create an initial workbook (basically - a consent form) to send that simply gets the serial number and username and, with that persons consent, then emails that info back to you, this workbook can then delete itself (optional) from the users machine. With that info you can then simply code the serial number and username into the copy you're going to send.

[ndendrinos]

Initially one person ... and I have no problem with sending the first book to get the serial number and have it emailed back to me. Wouold prefer to do it the other way and not have to ask for it and have fun with the recipient when that person tries to share the file with others....
Anyhow I thank you again ... I still leave this posting "unsolved" until this last question is solved ... I am no expert at Excel nor is the recipient so for now I do not worry about holes that could be exploited UNLESS there is a security reason. I do not see how by having the serial number of your Disk I can do harm to your computer or invade your privacy.

Thank you John .. it is late here so time to rest ... to you and ALL (do not want to get the one that pours water over flames upset) I say ... until tomorrow.

[johnske]

The major hole in the other way is that the original file sent (probably zipped) after extraction, has to get all the info and set it up so that it's initialized. This makes that copy useable only on that machine but leaves the original unitialized file untouched, and it can be easily copied and sent on to others for them to do the same

[Charlize]

The major hole in the other way is that the original file sent (probably zipped) after extraction, has to get all the info and set it up so that it's initialized. This makes that copy useable only on that machine but leaves the original unitialized file untouched, and it can be easily copied and sent on to others for them to do the same

I would specify a certain time to the initial install. After that time the initial install fails. To store the unique id of the drive I would use a secret xl hidden sheet named ConfigPassed where you store the id that you fetched the first time (I think it's not possible to unhide using the menubars). The next time we check against the id we stored and the id we fetched from the running computer because the configuration tab is deleted and doesn't exist the program looks for the ConfigPassed tab and uses this.

Charlize

[Charlize]

The initial installation time without configuration tab. First check on ConfigPass. If not present do the initial installationcheck and create ConfigPass to store the number.
[vba]Sub date_check()
'If current date - 7 = 1st of Novembre and time less than 12:00
'So you've got 10 minutes to do the first installation
'Once this time is passed they must guess that the date = 8 novembre
'+ time to do the installation (between 11:50 and 12:00)
If Date - 7 = #11/1/2006# And Time < TimeSerial(12, 0, 0) And _
Time > TimeSerial(11, 50, 0) Then
MsgBox ("Initial installation")
Else
MsgBox ("Contact me for new installationfile")
End If
End Sub[/vba]
Charlize

[ndendrinos]

John, Charlize ... I'm leaving to work in 15 min , When back this evening I will work on it.Your idea Charlize in inserting an expiry date in the file zipped or not is the solution and that I can do ... the business of the hidden sheet is tricky for I force the recipient to accept macros by hiding all sheets except the splash screen to the effect that to view the book you have to accept macros ,,, not sure if I can add to the code to unhide all sheets except "configuration."
Time is up ... have a great day and thanks a lot
Nick

[johnske]

Thanx Charlize, your idea has some appeal, but we don't know how long it will be before the email will be received, let alone when it will be opened - get the time too short and there's lots of problems, set it too long and there can be unauthorized copies sent out...

Ok, this is the other alternative and goes into the workbook that gets the details (I would suggest you NOT lock the VBA project for this - so the other can check that that's ALL that's being sent):

In the ThisWorkbook code module[vba]Option Explicit
'
Private Sub Workbook_Open()
Run "SendInfo"
End Sub
'
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'
' deletes this workbook (optional) uncomment if wanted
' On Error Resume Next
' With ThisWorkbook
' .Saved = True
' .ChangeFileAccess Mode:=xlReadOnly
' Kill .FullName
' .Close False
' End With
'
End Sub
[/vba]Same workbook, but in a standard module:[vba]
Option Explicit
'
Private Sub SendInfo()
'
'replace xxx@yyyy.com with your own email address
Const MyEmailAddy As String = "xxx@yyyy.com"
'
With Sheets(1)
.[B1] = "Hard-drive Serial Number"
.[B2] = CreateObject("Scripting.FileSystemObject").GetDrive("C:\").SerialNumber
.[C1] = "Logon User Name"
.[C2] = Application.UserName
End With
'
'write your own message below...
If MsgBox("By clicking ''Send'' In the email dialog that follows, you thereby give " & _
"your consent" & _
vbNewLine & _
"for me to encode the serial number of your hard-drive and your logged on username" & _
vbNewLine & _
"into a program that can only be used on this PC when logged on with that username..." & _
vbNewLine & _
vbNewLine & _
"The email dialog will appear shortly - CONTINUE?" & vbNewLine, _
vbYesNo, Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)) = vbYes _
Then Application.Dialogs(xlDialogSendMail).Show MyEmailAddy, _
Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
End Sub
[/vba]



In the ThisWorkbook code module for your book that's to be sent later (Lock the project - Note: you would also need to ensure macros are enabled, see here > http://www.vbaexpress.com/kb/getarticle.php?kb_id=578):[vba]Option Explicit
'
Private Sub Workbook_Open()
With Application
.EnableCancelKey = xlDisabled
Call ConfirmUser
.EnableCancelKey = xlInterrupt
End With
End Sub
'
Private Sub ConfirmUser()
'
'replace 1234567890 below with the real number
Const HardDriveSerial As Long = 1234567890
'
'replace Authorized User below with the real username
Const LogOnName As String = "Authorized User"
'
With Application
If Not CreateObject("Scripting.FileSystemObject") _
.GetDrive("C:\").SerialNumber = HardDriveSerial _
Or Not .UserName = LogOnName Then
.DisplayAlerts = False
.Quit
End If
End With
'
End Sub[/vba]

[Charlize]

Timetrial installation + serialnumber saving. The time limit for the installation is indeed a problem. Your (Johnske) solution is an elegant way. Another registration ...

[vba]'These three lines are in a normal module
Option Explicit
Option Private Module
Public driveno As Long
Sub check_for_sheet()
'This one goes in the workbook open event. Make sure macro's are enabled.
'Tip: One sheet named info is visible. Everything else is xlveryhidden.
'If they don't allow macro's sheet info is visible to say macro's must be enabled
'If macro's are enabled you could unhide a sheet with xxx = xlvisible
If Not SheetExists("ConfigPass") Then
date_check
Else
DriveSerialNumber
If driveno <> Worksheets("ConfigPass").Range("A1").Value Then
MsgBox ("Your not allowed to open this file on this computer !!!")
Application.Quit
Else
MsgBox ("Go ahead, everything just fine")
End If
End If
End Sub
Sub date_check()
'This macro is a normal module. We can hide a complete module with option private module
'So the macro's aren't visible.
'If current date - 7 = 1st of Novembre and time less than 14:00
'So you've got 30 minutes to do the first installation
'Once this time is passed they must guess that the date = 8 novembre
'+ time to do the installation (between 14:00 and 13:30)
If Date - 7 = #11/1/2006# And Time < TimeSerial(14, 0, 0) And _
Time > TimeSerial(13, 30, 0) Then
MsgBox ("Initial installation")
'okidoki add the sheet ConfigPass
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "ConfigPass"
'Worksheets("ConfigPass").Visible = xlVeryHidden
'Remove mark for above line to hide the sheet ConfigPass
DriveSerialNumber
Worksheets("ConfigPass").Range("A1").Value = driveno
Sheets(1).Select
'ActiveWorkbook.Save
'Saves workbook with configpass and drivenumber
'we can even add the logon name
'but indeed, it must be done within a certain time. Maybe make a phonecall
'before sending it over ?
Else
MsgBox ("Contact me for new installationfile")
End If
End Sub
Sub DriveSerialNumber()
'normal module
driveno = CreateObject("Scripting.FileSystemObject").GetDrive("C:\").SerialNumber
End Sub
Function SheetExists(SheetName As String) As Boolean
' normal module
' function to determine if a sheet exists or not
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) > 0 Then
SheetExists = True
Exit Function
End If
NoSuchSheet:
End Function
[/vba]
Charlize

You could say that all worksheets are visible exept for ConfigPass and Intro. But when closing you should reverse it so that intro is visible but all the others aren't.

[ndendrinos]

John this is just perfect and I cannot thank you enough.
By your perseverance you have solved my problem, I?ve learned from it and sure enough many more on this forum will benefit from it.
I'm doubly pleased that Charlize is also impressed by your solution.
I would like to take this opportunity to thank you too Charlize for the great effort you?ve done here. I will take your solution to fruition as well.
So until I get both solutions running this posting stays open.
I am really appreciative of the willingness the likes of you both show in helping out others.
Sincerely
Nick

[ndendrinos]

Charlize I just finished testing your idea and it works great.I attach the file for your approval ... I will add code to destroy the file if used on another computer than the one it is intended to work on. I removed the time element and just left the date. It is fixed for 11/08/2006
There is one thing that escapes me and that is:
Instead of using a time why not make the workbook code run just once on a particular date ?
Anyhow ... not finished yet but maybe very soon.
Regards,
Nick