Solved: Only you and no one else

[ndendrinos]

Hello
I read a lot on how to try and protect againt save, save as,etc...
My goal is this:
I send you a file and only you can use it.
If you choose to make a copy on diskette or other and give it to someone else the file will not work on his or her computer.

Possible solution ... this is where I need you input.
Before I send you the file I ask that you give me the "path" of you desktop.
Mine is : C:\Documents and Settings\nick dendrinos\Desktop\test.xls

On open If path not above then workbook close.
The VBA is password protected .... this is already in place
The file will self destruct after a certain date....this is already in place

Is this possible ? is there a better way you can think of ? And if possible when on a computer other than the one it is intended to work how can the VBA be stopped from opening ? will it open where the error is BUT still ask for a password?

Thank you for your time

[Charlize]

Maybe use a one time macro on opening the sheet. When this happens, place an ini-file or something in his directory. When copying the sheet, the macro for making this ini isn't there so start up of workbook will fail because this ini isn't present. So your friend wouldn't know it that you are willing to share your work with him but not with his friends.
Charlize

[ndendrinos]

Thank you and great idea Charlize .... much better than asking for "path"

I know how to make a macro run once on open BUT I use the self delete for that.... and ini. file I have no clue...so unless I get help with the code
can I call a short macro on open (something trivial not even visible) and have that macro self destruct after a certain number of days instead ?
Thanks
Nick

[Bob Phillips]

Use the registry.

Look at GetSetting and SaveSetting in VBA help.

[ndendrinos]

Thank you xld ... looked at the VBA help and came up empty ...

I like Charlize's idea (because I understand some of it)
What I'm working on is an on open macro that will call a sheet macro ...
And I can't get it to work
Here is the file

AND IF I COPY THE WORKBOOK THE SHEET MACRO IS PART OF THAT COPY .... so not a good idea.

[mdmackillop]

GetSettings and SaveSettings example
http://vbaexpress.com/kb/getarticle.php?kb_id=208

[ndendrinos]

Thank you mdmackillop I would prefer to leave the registry alone ... I have done some progress but now need code for this:
On open look for folder LogFiles in"C\"
If not there ,,, application.quit

Here is what I have so far ,,, I will make the initial macro that creates the folder LogFiles in C\ self destruct when the workbook is opened the first time ever ... Charlize refers to a one time macro ... does anyone have code for this ?

[johnske]

...I will make the initial macro that creates the folder LogFiles in C\ self destruct when the workbook is opened the first time ever ... Charlize refers to a one time macro ... does anyone have code for this ?

That's easy - but you already said the VBA project's password protected, so that's not gonna work...

[ndendrinos]

Hello John, Not sure I understand why it would not work if the VBA is password protected. The initial macro that creates the folder LogFiles will self destruct leaving the folder LogFiles on the user's C\ intact.
Next time the user opens the file an additional macro will check for LogFiles and if not found the file will close.
The problem in my mind is how to have two on open macros.
Can you elaborate more please?
Thank you

[johnske]

For security reasons you cannot programmatically change any code in a code pane in the VBE window when the VBA Project is password protected. So you can only delete the macro when the project has been left (sent out) unprotected.

If you have an additional macro that you want to keep to check for a log file, this macro is also then unprotected and all the info regarding the log file is visible to anyone that cares to look.

[johnske]

PS: Of course, you can always send it out unprotected with a macro to delete the procedure or module to be deleted and then programmatically lock it afterwards. But the only way I know of to do this is with SendKeys - as shown below - and that's unreliable... [VBA]Option Explicit

Sub LockVBAProject()

With Application

'//execute the controls to lock the project\\
.VBE.CommandBars("Menu Bar").Controls("Tools") _
.Controls("VBAProject Properties...").Execute

'//activate 'protection'\\
.SendKeys "^{TAB}"

'//CAUTION: this either checks OR UNchecks the\\
'//"Lock Project for Viewing" checkbox, if it's already\\
'//been locked for viewing, then this will UNlock it\\
.SendKeys "{ }"

'//enter password (password is 123 in this example)\\
.SendKeys "{TAB}" & "123"

'//confirm password\\
.SendKeys "{TAB}" & "123"

'//scroll down to OK key\\
.SendKeys "{TAB}"

'//click OK key\\
.SendKeys "{ENTER}"

'the project is now locked - this takes effect
'the very next time the book's opened...
End With

End Sub[/VBA]

[ndendrinos]

Password = max
You are right ,,, can I on open (on an unprotected VBA) run the code and then protect the VBA by adding code to the macro kills self ?
Here is test3 that does not work for the reason you have explained.

John I respect your opinion . If what I wish to do is not possible can you suggest something else besides fooling around with other people's regisries? If not I will call it a night.
Thank you again and thank you all

[johnske]

Seems you were replying when I posted my PS

[ndendrinos]

John thanks a ton !
Works great for me ... if you are still watching my posting in what way do you feel it is unreliable? what I mean is this is a one time thing... once the VBA is locked is there a chance it will unlock all by itself ?

And to all I still need code to :
On open check if folder LogFiles is there and if not application=quit

Thank you ALL

[johnske]

John thanks a ton !
Works great for me ... if you are still watching my posting in what way do you feel it is unreliable?

I don't have a very great deal of experience with SendKeys and i've never had it fail yet, but the consensus of opinion is that using SendKeys to do things is regarded as being "unreliable" - so I just tend to go along with that

...what I mean is this is a one time thing... once the VBA is locked is there a chance it will unlock all by itself ?..

No

[ndendrinos]

Good enough for me John ... And thanks again.

I do not mark this "solved" yet... need to solve the :
" On open check if folder LogFiles is there and if not application=quit" part.
It's been a fun evening and I learned something ... I'll leave while I'm ahead and will revisit tomorrow.
Thank you all

[johnske]

Good enough for me John ... And thanks again.

I do not mark this "solved" yet... need to solve the :
" On open check if folder LogFiles is there and if not application=quit" part.
It's been a fun evening and I learned something ... I'll leave while I'm ahead and will revisit tomorrow.
Thank you all

You'll find all the bits and pieces for doing that here

[johnske]

... in what way do you feel it is unreliable? ...

Regarding your question above, I found this...

...The main problem with SendKeys is that the code usually runs faster than Windows can carry out the required activity - this is especially so if manipulating an external application or opening a dialog box or menu. We therefore need to slow the action down by using Wait statements. SendKeys also has its own "Wait" argument True or False...

and in the VBA Help files regarding SendKeys: Wait Optional Variant. True to have Microsoft Excel wait for the keys to be processed before returning control to the macro.

[Charlize]

This will check for presence of a file :
[vba]Sub check_for_file()
If Dir("C:\Logfile.txt") = Empty Then
MsgBox ("File doesn't exists")
Else
MsgBox ("I'm present so go ahead")
End If
End Sub[/vba]
Charlize

ps.: Maybe change the attribute of logfile.txt to hidden.
Regarding the one time thing. You could use a hidden sheet named configuration. If that sheet exists create the logfile and delete the sheet, else goto the check of the file.

[Charlize]

A little function and demo to show what I mean.
[vba]Function SheetExists(SheetName As String) As Boolean
' 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

Sub check_for_sheet()
' First check for logfile and then for sheet
' if logfile exists then it's ok
' maybe case statement will work to
' the sheet Configuration is a xlveryhidden sheet
If Not SheetExists("Configuration") Then
MsgBox ("You haven't got a licence !!!") & vbCrLf & _
"If you want a working copy, contact me !!!"
Else
' Here you create the logfile and delete the sheet
MsgBox ("A first time activating workbook !") & vbCrLf & _
"Please wait until ready !!!"
' delete sheet with following lines
' application.displayalerts = false
' sheets("Configuration").delete
' application.displayalerts = true
End If
End Sub[/vba]
Charlize