On the sheet protection thing, since you're using macros anyway, you only need ONE line in your macro to give the macro permission to make changes on your protected sheet.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim N As Double
Worksheets("Sheet3").Protect "1234", UserInterfaceOnly:=True
If Not Intersect(Target, Range("$E$2:$E$301")) Is Nothing And Target.Cells.Count = 1 Then
N = Abs(Target)
If N > 60 Then
MsgBox "Number too high" 'optional, remove if unhelpful
Target.Select
GoTo ExitOut
End If
Application.EnableEvents = False
Set Cell = Cells(Target.Row, 5 + N)
If Target.Value < 0 Then
Cell = ""
Target.ClearContents
Target.Select
Else
Cell = N
Target.ClearContents
Target.Select
End If
End If
ExitOut:
Application.EnableEvents = True
End Sub
Note...if you're going to keep automating on this sheet, you'll have to keep setting that UserInterfaceOnly:=False flag...so you might want to just do it once and for all in a workbook_open event macro.
Private Sub Workbook_Open()
Worksheets("Sheet3").Protect "1234", UserInterfaceOnly:=True
End Sub
With that inserted into the ThisWorkbook module, it runs when the book first opens and your other macros won't all need to do it themselves.
Alternately, another worksheet event macro that goes in the sheet module(s):
Private Sub Worksheet_Activate()
Worksheets("Sheet3").Protect "1234", UserInterfaceOnly:=True
End Sub
Just some parting thoughts.
Bookmarks