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 :biggrin:
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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 :biggrin:), 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

G
  • Question
Disable Cut Copy Paste
Replies
1
Views
333
jkpieterse
jkpieterse
J
  • Solved
Disable shapes when Workbook is protected VBA
2
Replies
12
Views
1K
mumps
mumps
Y
  • Question
Conditional formatting
Replies
3
Views
356
SanjayGMusafir
SanjayGMusafir
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
799
Insurance Dave
I
S
  • Solved
Conditional format dates in a range which are x days from today
Replies
3
Views
864
Sallyprit
S

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

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