Home » Forums » Open Source Learning Center » Question and Answer

VBA Code to prevent changes in an Excel Workbook

Submitted by zNyteAngel on 13 December, 2011 - 15:36

Hi!

I need help with a VBA Code that is giving me an error:

Private Sub Worksheet_Activate()
With ActiveSheet
If Date > CDate(.Name + 5) Then
MsgBox "No changes allowed"
End If
.Unprotect "PROTECT"
.Cells.Locked = True
.Protect "PROTECT"
End With
End Sub

The error keeps happening in the:
If Date > CDate(.Name + 5) Then

Here is what I am trying to do:
I have an excel workbook with multiple tabs that are labeled as a "date". I want it to read the active sheet name "date" add 5 days, and if someone goes into the workbook after that 5 day period, they can no longer make any changes to those sheets.

Can someone help me???

Thanks!

‹ excel export data to internet form Import Multiple Text Files at once. ›
»
Vishesh's picture

Try this If Date >

Submitted by Vishesh on 14 December, 2011 - 04:17.

Try this

If Date > (CDate(.Name) + 5) Then

slight change in the bracket position.

»
Useful's picture

Try this code: Private Sub

Submitted by Useful on 31 December, 2011 - 15:07.

Try this code:

Private Sub Worksheet_Activate()
Set data = ActiveSheet
With ActiveSheet
data = DateValue(data.name)
If Now - data >= 5 Then
MsgBox "No changes allowed"
End If
.Unprotect "PROTECT"
.Cells.Locked = True
.Protect "PROTECT"
End With
End Sub

»