×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Spreadsheet Expiration Date

Spreadsheet Expiration Date

Spreadsheet Expiration Date

(OP)
All:

I previously posted a request for some input on putting expiration dates on spreadsheets and got some great input that allowed me to get that set up.

A couple of follow up questions:

1) I have the code password protected in VBA, but is there a way to prevent users from simply clicking the disable macros tab on start up?

2) As a follow up to 1) above, a former poster had made the following recomendation - "I would put conditional formating that will make the cells all black if the NOW() function is pass certain date.  And maybe put a red statement saying it expires.  Make sure you put a password so people cant edit it".  The question is how would I add conditional formatting?  (at the risk of stating the obvious - I'm not an Excel or VBA expert)

The code I am using is (also from a helpful previous poster):

Auto_Open()
ActiveWorkbook.PrecisionAsDisplayed = False
If Date < #3/26/2009# Then Exit Sub
Bye_Message
With ThisWorkbook
.Saved = True
.ChangeFileAccess xlReadOnly
 ' Kill .FullName
.Close False
End With
End Sub

Bye_Message()
'Self-Destruct Final Message no response needed, just OK
ln1 = "This spreadsheet has a defined expiration date which has expired.  After the expiration"
ln2 = "date the spreadsheet will not function.  Your data will not be lost.  Send the file to the"
ln3 = "author to request an updated copy."
In4= " "
ln5 = "Author is Greg Test (test@yahoo.com / www.test-consulting.com)
msg = ln1 & vbCr & ln2 & vbCr & ln3 & vbCr & ln4 & vbCr & ln5
style = vbOKOnly + vbInformation
Title = "International Test Consulting, LLC© December 2008,  by Greg"
MsgBox msg, style, Title
End Sub

Any help would be appreciated.

Greg Lamberson, BS, MBA
Consultant - Upstream Energy
Website: www.oil-gas-consulting.com
 

RE: Spreadsheet Expiration Date

One method is to make all the sheets of interest "very hidden".

Leave one sheet visible, and on it put "Sorry, Expired" or whatever message you care.

Your AutoOpen code - upon confirming that things have not expired can hide the "expired" sheet and make the very hidden sheets visible.

If someone turns macros off all they see is the "expired" sheet.

It's not bullet proof, but it will discourage most folks.

RE: Spreadsheet Expiration Date

Conditional formatting does not require VBA, it is built in to Excel.

View Clyde Hancock's profile on LinkedIn

RE: Spreadsheet Expiration Date

I believe that the Excel user interface can be manipulated through VBA, so you might be able to delete all the menu and toolbar methods for accessing the macros.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources