How to: Make user click End User License Agreement acceptance

[jasonsweeney]

My company wants to send an internally created and sophisticate
spreadsheet to another company.

My boss wants to make sure that any users at the other company that us
the speadsheet have to click "accept" on one of those End User Licens
Agreement useforms that we all see when we use new software.

I have the text of the License Agreement in a word document.

Does anybody have some code out there for the userform and/or the cod
that makes the user click "accept" only once, and has the spreadshee
record that the user clicked "accept"?

thnx in advance

 

[John Wilson]

jason,

One way...................

Create a UserForm (UserForm1) with a text box or whatever for your
EULA.
On that form place two Buttons; CommandButton1 ("Accept") and
CommandButton2 ("Decline")

Copy this code to the Workbook code (Alt + F11 and DblClick on
"ThisWorkbook")

Private Sub Workbook_Open()
CheckEULA
End Sub

Right click on ThisWorkbook and choose Insert/UserForm
In the UserForm1 code:

Private Sub CommandButton1_Click() ' Accept
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = True
Next ws
Worksheets("EULA").Range("A1") = "X"
Worksheets("EULA").Visible = xlVeryHidden
ThisWorkbook.Save
Unload UserForm1
End Sub
Private Sub CommandButton2_Click() ' Decline
Unload UserForm1
Application.DisplayAlerts = False
ThisWorkbook.Close
End Sub

And in a regular module:
Right click on ThisWorkbook and choose Insert/Module

Sub CheckEULA()
If Worksheets("EULA").Range("A1") = "" Then
UserForm1.Show
End If
End Sub

Now create a new sheet and name it EULA.
On that sheet, put a warning or whatever that they need to enable
macros for the workbook to open.
Lastly..the important part.
From the VBA Editor, select all of the sheets (except "EULA")
and set the sheet property to VeryHidden.

How it works.......
When the workbook opens (with macros enabled) it'll call the
CheckEULA sub. That sub will open the UserForm only if
it finds nothing in A1 on the EULA sheet.
If they decline, the workbook closes.
If they accept, All the sheets are unhidden and the EULA sheet
is made VeryHidden. Before doing that, it puts an "X" in A1 on
the EULA sheet and then saves the workbook. The next time
it opens, it will see the "X" in A1 and never show them the
EULA sheet or the UserForm.

Note: Any protection scheme like this can be bypassed by
an experienced user.

John

 

[John Wilson]

jason,

Some further notes on the above reply......

The reason that you need to VeryHide all but the EULA sheet is
so that when the workbook opens, it'll be the only sheet visible
to the user (they can't easily unhide the sheets either) .
If they open it without macros enabled, they won't get the UserForm
either, which is why you need to have a note on that sheet telling
to enable macros and then close and reopen the workbook (you should
place the note behind where the UserForm will pop up).

Oh, and you might as well password protect the VBA too (from the
VBA editor Tools/VBAProjectProperties/Protection
Check "Lock Project for Viewing" and enter passwords).

Again, an experienced user (or one with access to these ng's or Google)
can easily circumvent this coding (whether protected or not).

John

jason,

One way...................

Create a UserForm (UserForm1) with a text box or whatever for your
EULA.
On that form place two Buttons; CommandButton1 ("Accept") and
CommandButton2 ("Decline")

Copy this code to the Workbook code (Alt + F11 and DblClick on
"ThisWorkbook")

Private Sub Workbook_Open()
CheckEULA
End Sub

Right click on ThisWorkbook and choose Insert/UserForm
In the UserForm1 code:

Private Sub CommandButton1_Click() ' Accept
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Visible = True
Next ws
Worksheets("EULA").Range("A1") = "X"
Worksheets("EULA").Visible = xlVeryHidden
ThisWorkbook.Save
Unload UserForm1
End Sub
Private Sub CommandButton2_Click() ' Decline
Unload UserForm1
Application.DisplayAlerts = False
ThisWorkbook.Close
End Sub

And in a regular module:
Right click on ThisWorkbook and choose Insert/Module

Sub CheckEULA()
If Worksheets("EULA").Range("A1") = "" Then
UserForm1.Show
End If
End Sub

Now create a new sheet and name it EULA.
On that sheet, put a warning or whatever that they need to enable
macros for the workbook to open.
Lastly..the important part.
From the VBA Editor, select all of the sheets (except "EULA")
and set the sheet property to VeryHidden.

How it works.......
When the workbook opens (with macros enabled) it'll call the
CheckEULA sub. That sub will open the UserForm only if
it finds nothing in A1 on the EULA sheet.
If they decline, the workbook closes.
If they accept, All the sheets are unhidden and the EULA sheet
is made VeryHidden. Before doing that, it puts an "X" in A1 on
the EULA sheet and then saves the workbook. The next time
it opens, it will see the "X" in A1 and never show them the
EULA sheet or the UserForm.

Note: Any protection scheme like this can be bypassed by
an experienced user.

John

 

[jasonsweeney]

Thanks John, this is very helpful.

One stone in the shoe...even after the user accepts the EULA, I stil
want several of my worksheets to remain hidden. Is it better to als
make these sheets veryhidden, or just normal hide?

(Side question, when a worksheet is veryhidden, can Excel see th
data?, i.e. can I have the visible worksheets pull data from cells i
the veryhidden worksheet?

 

[John Wilson]

jason,

I still want several of my worksheets to remain hidden.

Not a problem.
Get rid of the For..Next loop that unhides all of the worksheets
and just unhide each one that you want to.
Worksheets("yoursheetname1").Visible = True
Worksheets("yoursheetname2").Visible = True
Just make sure that you don't hide the EULA sheet until you've
made at least one other sheet visible or the macro will crash
(there always has to be at least one visible sheet)

when a worksheet is veryhidden, can Excel see the data?

Sure can.

John

 

[Tom Ogilvy]

Since John kind of glossed over it:

When the workbook opens (with macros enabled) it'll call the

If the user disables macros then your workbook is exposed. If you want code
to remove workbook and sheet level password protection, just search the
google groups archive of this group. the code can be run from another
workbook which does not have macros disabled.

You may be doing lot of huffing and puffing for nothing.

 

[jasonsweeney]

I hear ya....

The one bit of good news is the end users here will likely NOT be the
type that can work around this type of code. I think it will work for
this group.

 

[Tom Ogilvy]

LOL,

 

[John Wilson]

Tom,

If the user disables macros then your workbook is exposed

I did mention in my original post to jason that he should manually
make all the sheets except the "EULA" one veryhidden.
Since he only wanted a one-time shot at this, I figured that doing
it manually would be the easiest solution.
If he saves it and sends it out that way, his workbook won't be
exposed unless they enable macros and click the "Accept"
button.

jason,
To insure that the workbook is saved correctly, you could
use the following in the Before Save Event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Worksheets("EULA").Range("A1") = "" Then
Worksheets("EULA").Visible = True
Worksheets("EULA").Activate
Worksheets("yourworksheet1").Visible = xlVeryHidden
Worksheets("yourworksheet2").Visible = xlVeryHidden
' etc.
End Sub

The above will check for a value (any value) in Range("A1")
of the EULA sheet. If the cell is blank, It'll set it up the way
that you want it to be sent out.

Regards,
John

Since John kind of glossed over it:

When the workbook opens (with macros enabled) it'll call the

If the user disables macros then your workbook is exposed. If you want code
to remove workbook and sheet level password protection, just search the
google groups archive of this group. the code can be run from another
workbook which does not have macros disabled.

You may be doing lot of huffing and puffing for nothing.

 

[John Wilson]

jason,

Missed an End If just before the End Sub in that code I just posted.

John

Tom,

If the user disables macros then your workbook is exposed

I did mention in my original post to jason that he should manually
make all the sheets except the "EULA" one veryhidden.
Since he only wanted a one-time shot at this, I figured that doing
it manually would be the easiest solution.
If he saves it and sends it out that way, his workbook won't be
exposed unless they enable macros and click the "Accept"
button.

jason,
To insure that the workbook is saved correctly, you could
use the following in the Before Save Event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Worksheets("EULA").Range("A1") = "" Then
Worksheets("EULA").Visible = True
Worksheets("EULA").Activate
Worksheets("yourworksheet1").Visible = xlVeryHidden
Worksheets("yourworksheet2").Visible = xlVeryHidden
' etc.
End Sub

The above will check for a value (any value) in Range("A1")
of the EULA sheet. If the cell is blank, It'll set it up the way
that you want it to be sent out.

Regards,
John

 

[jasonsweeney]

New Problem.

With the code in the thread above, there is a problem:

Lets say I am a new user. I open the Excel sheet, and accept the terms
of the EULA. Then I save the workbook.

THEN, I send my friend John Doe a copy of the workbook. When HE opens
it, there is already an "X" in range("A1") of the Eula and thus John
Doe is not asked to accept the terms of the Eula Agreement.....

Any ideas on how to prevent that from happening?

 

[John Wilson]

jasonsweeney,

Here's one easy way.
Let's assume that you and your friend do not have the same
user names (Tools/Options/General/User Name).

Change this line of code (Accept Button):
Worksheets("EULA").Range("A1") = "X"
to
Worksheets("EULA").Range("A1") = Application.Username
when the user "Accepts", his/her UserName will be saved in A1.

and this line of code (CheckEULA Sub):
If Worksheets("EULA").Range("A1") = "" Then
to
If Worksheets("EULA").Range("A1") <> Application.UserName Then
The above will see if A1 equals the UserName. If it doesn't,
it'll show the userform.

There are other things to check besides the UserName (hard drive
serial number, machine name, operating system name).
A quick search of Google will turn up threads on how to
get them and you could use them in place of the Username.

You might also want to hide column A or row 1 on the EULA
sheet so the user doesn't ever see what's going in there (or
just change the font color of A1 to white).

John

 

[jasonsweeney]

John,

I aimed at the same result by different route. One the user accept
the Eula, another "registration" user form comes up. This form gather
some information and pastes the user's name into cell A1 of the Eula.

In a different part of the spreadhseet there is a cell that asks th
user to enter their name. If the user uses registration, then thi
cell (lets call it Sheet1.target("B1"), herein after "Workbook Name"
is already filled in for them via the userform.

If a person changes the Workbook Name then I am trying to launch
macro that hides all the sheets, and shows the Eula userform again....

I having problems coding this, however. I just posted another thrrea
tying to solve this problem, namely that I am having difficulty tryin
to lauch the macro when Sheet1.target("B1") does not matc
Eula.Target("A1")