VBA Form T's & C's

[Portucale]

Hi,
A bit of a muddle and honestly I don't know where to start, I was given a challenge, as on a specific Excel report that we have in place, need to have a pop up with the T's & C's, which the user then to either accept (continuing into the report) or not accept (which the report would close), not only that we need that the message only appears ONCE for the same user :'(, so in brief this is what I am looking for:

• VBA Form with the Terms and Conditions, preferred option, or a link to a file/webpage, if user want to read them
• Aggree/Disagree radio buttons
• If first time user accessing the report and if T's & C's agreed then the UserID would go to a sheet within the workbook
• Form to appear ONLY if the user is visiting report for the first time, or didn't agree with T's & C's before, if user already has accepted the T's & C's then form would not reappear

Please do not take this as I want someone to do the work for me but any help and ALL help is appreciated

Thanks in advance,

 

[Portucale]

OK, I have now my VB form which loads on worksheet_Open, so far so good, although a few things need to be adjusted:

• Text in textbox to be read only
• First line to be always visible when the user clicks outside the textbox or when the form loads

Again Many thanks for the help,

 

[SirJB7]

Hi, Portucale!

A few questions:
a) Where's the workbook stored? Local or network.
b) Is it read only or users have write access?
c) The only once is for machine or for user?
d If for user, how do you identify it?

Regards!

 

[Portucale]

Hi SirJB7,

Answers to the questions...

a) Where's the workbook stored? Local or network.
The book is stored in a Network location book is downloaded via a URL link to the local Intranet
b) Is it read only or users have write access?
The access is read only, but I believe we can bypass this with a procedure which than would log the UserID in a location within the book
c) The only once is for machine or for user?
Is for the User identified by UserID/LoginID
d) If for user, how do you identify it?
See above

Many thanks for the help,

 

[SirJB7]

Hi, Portucale!
So:
a) You can update the workbook.
b) The workbook can't be opened from the network location, it must be downloaded and then opened from the local machine.
c) What if user X accesses the file from machine M and then from machine N?
Regards!

 

[Portucale]

Hi,

You know, now at home and more carefully reading your questions and my brief I've just realized that not only I have wasted your time I also wasted my day :'( this task cannot be done as the book when opened by the users is read only as it in a location that is read only. So really I do not know what is the solution.

 

[SirJB7]

Hi, Portucale!

I assumed that it should be of read only access but I expected that even in a networked location either that workbook or at least another common one to be writable.

A possible scenario might be this:
a) That workbook in folder R, access read
b) Another workbook in folder W, access read/write
c) That workbook should:
- have code in the workbook class module for the open event
- access to the another workbook
- check in a table (user, date_time) if actual user has already opened it
- if not prompt with the T&C; if accepted, mark in the another workbook the proper entry, if rejected quit
- if yes, go on

A 2nd possible scenario might be simulating a cookie in local machines and placing a similar logic as above in local files.

Regards!

 

[Portucale]

Hi SirJB7

Apologies for the long absence, technology issues.
After all the conversation and rush from higher management it was decided to 'scrap' the solution within the excel application and apply the concept into the webpages where the population download the spreadsheet. However and for clarification here we go with the answer to your questions;
- We would like to identify the users, date_time and if Accepted or Declined
- Not prompted with T's & C's if already accepted

In regards to simulate a cookie in local machines not sure if possible as we have Citrix (Wise) boxes, for the general population.

Many thanks for all the help

 

[SirJB7]

Hi, Portucale!
Glad you don't have to go on dealing with an Excel solution. And as it then becomes into an abstract issue, let us leave it as it is. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!

 

[SirJB7]

Hi, Portucale!
The only-once acknowledge should be very easy to implement using a dedicated networked workbook with 2 columns in a worksheet: Id & Date/Time. It'd be safer than any cookie-like method in the distributed user local machines.
Regards!