Prevent User From Accessing Data After a Date

[jencha]

Hello All of you wonderfully helpful people-

I have a question for you.

We are trying to get our sales team to send us data on their accounts in Xcel via a template that we will be sending out, but we do not want the field to go back to this spreadsheet to use it for territory management. We will be sending out a template, they will be filling it out and sending it back to us and we will then analyze the data.

So, we would like to add macros that cut off access to the users after a specific date but allow us access to the data for analysis.

Or, maybe there is an easier way to do this...

It would be great if this would restrict cutting and pasting from the spreadsheet as well, but not necessary.

Thanks in Advance!

Jenny

 

[jencha]

bumping, hoping for some ideas. Thanks!

 

[codeliftsleep]

Easiest way would be to write a few lines of VBA code to move the data to a different sheet after a certain date/number of days, set it to very hidden and password protect the code so they can't get in there and change it to unhide it. This would allow the people who know the password to unhide the sheet and access the data.

 

[jencha]

I'm pretty new to VBA. I am only experienced in copying and pasting haha... Do you know where I might find some references on this? Anyone really enjoy writing code and want to write this out for me?

 

[codeliftsleep]

I'm pretty new to VBA. I am only experienced in copying and pasting haha... Do you know where I might find some references on this? Anyone really enjoy writing code and want to write this out for me?

So are you looking for it to be after a certain date for all of them or after a certain number of days past the initial date?

 

[jencha]

After a certain date, January 29th 2018 we do not want them to have access.

 

[codeliftsleep]

After a certain date, January 29th 2018 we do not want them to have access.

O, so you don't want them to be able to access any of the data? This is even simpler then...if that is case

 

[jencha]

Exactly, we want to analyze the data though. Simple is goood!!!

 

[codeliftsleep]

Exactly, we want to analyze the data though. Simple is goood!!!

put this in the ThisWorkbook module

private sub Workbook_Open()
   
     If Date > CDate("01/28/2018") Then Sheets("mySheet").Visible = xlVeryHidden


End Sub

 

[jencha]

Sure, testing it with a coworker by changing the date to 12/20/17 and it will not run because it says it's out of range... Would you mind running through the code and telling me how to read?