apply codes to all new sheets added

[cybrkada]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Sheets("Sheet1").Range("B3").Value >= -Date And Sheets("Sheet1").Range(" B3").Value <= Date - 1 Then
        ActiveWorkbook.Unprotect Password:="password"
        MsgBox "This workbook is locked, please contact xxx@xxx.com"
    Else
        'MsgBox "unlocked"
    End If
End Sub

Currently the code is working in Sheet1 only. How do I enter it in a module so that it applies to all new sheets added on the workbook?

 

[cybrkada]

wait... i think, it's working now.. hold on...

 

[cybrkada]

I add new sheets by copying the previous sheet and just deleting values on some columns. This also copies whatever the code is in the previous sheet. Maybe it's the culprit. Can this be fixed?

 

[Andrew Poulsom]

If you don't want to copy the worksheet's VBA code copy the cells and paste into a new worksheet.

 

[cybrkada]

Our standard process is by copying the previous sheet, this retains formatting, we dont want copy pasting these into new cells. Are there other options Andrew?

 

[Andrew Poulsom]

Copying all the cells to a new worksheet will also retain formatting.

 

[cybrkada]

I see, how about this error message "unable to set the locked property of the range class"? excel did not let me edit cells on yesterday's sheet but column B is editable then throws the said error message.

 

[Andrew Poulsom]

Is the worksheet protected?

 

[cybrkada]

yess all cells are protected.

 

[cybrkada]

i mean the cells are locked and the sheet is not protected, sorry

 

[Andrew Poulsom]

In that case you shouldn't be able to edit the cell, so the Change event won't fire.

The code assumes that initially your worksheets are unprotected. If you try to change data on a worksheet whose name is less than today, your action will be undone, all cells will be locked and the worksheet will be protected. Thereafter, Excel will prevent you from changing any cells. You shouldn't see the error "unable to set the locked property of the range class".