Vba recognize first time startup

[sassriverrat]

Does anyone know how to write coding such that excel will recognize the first time a particular file is opened on a computer?

Situation- I have a fairly self-automated workbook/program I’ve written that does a lot on its own with menus and calculations and all- all written in vba- but I’d like excel to recognize the first time a file is put on a machine (I.e. transferred via jump drive or email download) and to therefore open a userform that otherwise wouldn’t be available (I.e. someone can “initialize” the program with their name and credentials and such). Make sense?

 

[Tetra201]

Under Windows, this is can be achieved by checking a registry key/value (usually, in HKCU):

- check if the registry key/value exists
- if yes, then do not show the "first-time" userform
- if no, then show the "first-time" userform and add the registry key/value

 

[sassriverrat]

Hmmm. So how might I go about doing this in vba? I guess a little confused since I need this to work by just sending a file to someone (and no tweaking on their part)

 

[Tetra201]

Here is a simple example:

Sub DemoTest()
    Dim s As String
    s = GetSetting("DemoTest", "Registration", "UserName")
    If s = "" Then
        s = InputBox("Please enter your name:")
        If s <> "" Then SaveSetting "DemoTest", "Registration", "UserName", s
    Else
        MsgBox "Welcome back, " & s
    End If
End Sub

 

[sassriverrat]

Sorry for the delay in response. This looks COOL! I'm excited to work on this!

Just to make sure I'm reading through this correctly (sorry if this is annoying...)

"s" becomes a name that is stored in the registry.
so excel checks if "s" is blank using getsetting
if "s" is blank, the name input becomes "s" using savesetting
if s is not blank, it says welcome back "s"

How would I go about clearing "s"?

Thank you very very much, indeed!

 

[sassriverrat]

This is COOL! Thank you very very much @Tetra201 !

one quick question to make sure I'm understanding this and how to go about working with it. "Getsetting" is going into the registry to look for "s". If "s" doesn't exist, it saves the input box as "s" using savesetting.

So, how do I go about deleting "s" so that a new "s" can be put in?

 

[Tetra201]

You can clear data from the UserName by running the following code:

Sub DemoTest_Clear()
    SaveSetting "DemoTest", "Registration", "UserName", ""
End Sub

 

[sassriverrat]

Awesome! Thank you!

One last question- is it, through this process, possible to create a “lockout” of sorts, like having a license key that requires renewals, where the machine recognizes an expiration date (x days as predefined in call A1 after the first initialization) and when the date nears the computer date, and the workbook would become Locked and require a code (via msg box) to reopen. Does that make sense?

 

[Tetra201]

You can use the same approach to store the expiration date (as cleartext or in some encrypted form) in the registry and query it every time your workbook is opened. However, anyone know knows how to edit the registry can easily circumvent such a lockout.

Also note that the suggested approach (via SaveSetting/GetSetting) works on a per-user basis. To have it "per computer", you would need to use more sophisticated registry access tools.

 

[sassriverrat]

thanks for your help @Tetra201. the help as been much appreciated!