Disable Worksheet on a specific date...

M

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
Hello,

I want to be able to disable the use of a Worksheet on a date x days from the date I first circulate it. (This is because the data held within it will be old by this stage.)

After the 'expiry' date has surpassed, should the user try to access the Workbook, I want a message to pop up stating that the user should contact me so that I can provide an updated version of the Worksheet.

Can anyone suggest the best way of doing this, please?

Many thanks in advance!

Matty :-D
 
Sort by date Sort by votes
try

Code: 
Private Sub Workbook_Open()

a = [a1].Value
b = Now()
c = b - a
If c > 90 Then
MsgBox ("This Workbook Contains Old data" & vbCr & vbCr & "Please Contact Me")
End If
End Sub

where A1 contains the creation date and the 90 is the number of day before expiry

HTH
 
Upvote 0
One option...

This goes in the This Workbook module
Code: 
Option Explicit

Dim Expiry_Date As Date
Const Aging_Interval = 2
Dim Sht As Worksheet

Private Sub Workbook_Open()
    Expiry_Date = Sheets("Expired").Range("A1").Value
    If Date >= Expiry_Date + Aging_Interval Then
        For Each Sht In ActiveWorkbook.Sheets
            If Sht.Name = "Expired" Then
                Sht.Visible = xlSheetVisible
            Else
                Sht.Visible = xlSheetVeryHidden
            End If
        Next Sht
    Else
        For Each Sht In ActiveWorkbook.Sheets
            If Sht.Name = "Expired" Then
                Sht.Visible = xlSheetVeryHidden
            Else
                Sht.Visible = xlSheetVisible
            End If
        Next Sht
    End If
End Sub

Create a sheet named Expired. Create a large texbox with the message you want the users to see.
In A1, put the distribution date. Make the text white to make it less obvious...
In the code, change Aging_Interval to the # of days before the workbook expires.

On or after the expiry date, the Expired sheet is displayed with all other sheets hidden. Needs some work but it's a start...

Denis
 
Upvote 0
use start-up macro

You can use a start up macro (i.e. one associated with ThisWorkbook

The following code should work

Sub Workbook_Open()
Dim DateDone as Date

DateDone="July 7, 2007"
If Now() > DateDone Then
MsgBox "Contact XXX for updated ersion of worksheet"
Me.Close
End If
End Sub
 
Upvote 0
You help is much appreciated, shippey121!

The Code works well, but can you remind me what Code I need at the start, in addition to this, so that the Macro runs as soon as the Workbook opens?

Would it also be possible to have a message that encourages the user to 'Enable Macros' right at the start, otherwise they might disable them, which would then prevent this Macro from running?

Any help you can offer is most welcome!

Many thanks.

Matty
 
Upvote 0
to make the code run on start up

right click a sheet tab and select View Code, down the left hand side you should see the name of your workbook and a list of all sheets contained in it, then there should be one called THIS WORKBOOK, click that and paste the code exactly as i posted it, now save the workbook and it should run when started providing the user enables macros,

you cannot get a message to popup before the user enables macros because the code simply wont run UNTIL the have,

one way to make sure is to use a macro to HIDE all sheets when the workbook closes and just leave one sheet visible informing them to enable macros, then on the open code it would unhide all sheets

post back if you require further help
 
Upvote 0
Hello All,

I have opted to use SydneyGeek's code in my Workbook, as it is the closest thing to what I wanted.

However, I have other Worksheets within the Workbook that I want to be hidden from users at all times, but I am not too hot at VBA (yet :-D), so I was hoping someone would be able to help me doctor the code to my needs.

The other Worksheets that I want to be hidden at all times are called: "Material Data", "Shipping Equipment" and "Pallet Types".

Moreover, if I want to unhide these Worksheets at a later date to change anything on them, what is the easiest way of 'revealing' them again? Is there a Macro I can run to unhide all hidden Worksheets? One that I could include in the Workbook, but that wouldn't be obvious to the average Excel user...

As always, thanks to anyone who can assist me with this.

Kind regards,

Matty
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Disable 'X' on Worksheet
Replies
4
Views
354
Pacman52
P
G
  • Question Question
Disable Cut Copy Paste
Replies
1
Views
1K
jkpieterse
jkpieterse
D
  • Solved
Date Issues with User Forms
Replies
4
Views
328
dmt32
D
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
284
Kaper
Kaper
A
  • Question Question
How sort files contains specific word based on modified dates
Replies
0
Views
147
abdelfattah
A

Forum statistics

Threads
1,226,797
Messages
6,193,055
Members
453,772
Latest member
aastupin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top