apply codes to all new sheets added

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jul 25, 2014

• #2

You can use this event procedure in the ThisWorkbook module:

Code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

Why are you unprotecting the workbook and telling the user that it is protected?

 

Upvote 0

C

cybrkada

New Member

Joined

Apr 8, 2014

Messages

42

• Jul 25, 2014

• #3

That's after playing around with the code, trying to figure out what is wrong and what to do, i am really lost!

This is really what I wanted

http://www.mrexcel.com/forum/excel-questions/794015-lock-cell-sheet-after-time-interval.html

but there are no replies yet

 

Upvote 0

C

cybrkada

New Member

Joined

Apr 8, 2014

Messages

42

• Jul 25, 2014

• #4

Hi Andrew,

I have already modified code from ActiveWorkbook.Unprotect Password:="password"
to ActiveWorkbook.Protect Password:="password"

However, after I added a new sheet, B3 on the new sheet still pulls up data on the first sheet. How do I get this fixed?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jul 25, 2014

• #5

Try this in the ThisWorkbook module:

Rich (BB code):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If CLng(Sh.Name) >= CLng(Format(Date, "mmdd")) Then Exit Sub
    Application.EnableEvents = False
    Application.Undo
    Sh.Cells.Locked = True
    Sh.Protect Password:="password"
    MsgBox "This worksheet is locked, please contact xxx@xxx.com"
    Application.EnableEvents = True
End Sub

 

Upvote 0

C

cybrkada

New Member

Joined

Apr 8, 2014

Messages

42

• Jul 25, 2014

• #6

This gets yellow hightlighted in debug mode whenever i change the value of B3

Code:

If CLng(Sh.Name) >= CLng(Format(Date, "mmdd")) Then

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jul 25, 2014

• #7

On what worksheet? You said your worksheets had names in the format mmdd.

 

Upvote 0

C

cybrkada

New Member

Joined

Apr 8, 2014

Messages

42

• Jul 25, 2014

• #8

My bad, I am working on a test sheet, ok i will be using now the actual sheet, do I need to modify "Sheet1" by the actual first sheet on MMDD format?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jul 25, 2014

• #9

In case one or more sheets aren't named in the format mmdd, add this line at the beginning of the procedure:

Code:

If Not IsNumeric(Sh.Name) Then Exit Sub

 

Upvote 0

C

cybrkada

New Member

Joined

Apr 8, 2014

Messages

42

• Jul 25, 2014

• #10

Hi Andrew, still doesnt work, by the way in B3 i have =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256) which pulls up whatever the sheetname is (mmdd). I should not be able to edit cells on any sheet earlier than today's date. However, it does not let me edit cells even on 07/25 which is today's date.

 

Upvote 0