Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Make user click End User License Agreement acceptance
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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Make user click End User License Agreement acceptance
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 " wrote in message ... My company wants to send an internally created and sophisticated spreadsheet to another company. My boss wants to make sure that any users at the other company that use the speadsheet have to click "accept" on one of those End User License 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 code that makes the user click "accept" only once, and has the spreadsheet record that the user clicked "accept"? thnx in advance. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Make user click End User License Agreement acceptance
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 "John Wilson" wrote in message ... 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 " wrote in message ... My company wants to send an internally created and sophisticated spreadsheet to another company. My boss wants to make sure that any users at the other company that use the speadsheet have to click "accept" on one of those End User License 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 code that makes the user click "accept" only once, and has the spreadsheet record that the user clicked "accept"? thnx in advance. --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Make user click End User License Agreement acceptance
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? -- Message posted from http://www.ExcelForum.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Make user click End User License Agreement acceptance
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 "jasonsweeney " wrote in message ... Thanks John, this is very helpful. One stone in the shoe...even after the user accepts the EULA, I still want several of my worksheets to remain hidden. Is it better to also make these sheets veryhidden, or just normal hide? (Side question, when a worksheet is veryhidden, can Excel see the data?, i.e. can I have the visible worksheets pull data from cells in the veryhidden worksheet?) --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Make user click End User License Agreement acceptance
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? --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Make user click End User License Agreement acceptance
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 " wrote in message ... 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? --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to: Make user click End User License Agreement acceptance
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") -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My end user license agreement pops up when i open excel. How do ? | Excel Discussion (Misc queries) | |||
How can I stop the end user License agreement from popping up | Excel Worksheet Functions | |||
Office End-User License Agreement | Excel Discussion (Misc queries) | |||
End User License Agreement | Excel Discussion (Misc queries) | |||
How do i stop the End user License Agreement from Popping Up upon. | Excel Discussion (Misc queries) |