I've just had a questions answered about a worksheet change event macro which works perfectly except when I protect the sheet? sorry, as didn't realize this would initially be an issue.
how can I get around this without giving them access to the entire sheet. I'm trying to keep columns H and I fully locked and no editing abilities whatsoever so the worksheet change event can copy the formula down when data in column A is entered. I'm trying to restrict/prevent the user's ability to change the calculation that occurs in H and I.
I've seen password protection removed at the beginning of a macro and then reinstated at the end of a macro. but this defeats the protection of column H and I which is now what I need and the worksheet change event to still work.
macro is now as follows:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A4:A" & Rows.Count)) Is Nothing Then
If Target <> "" Then
Range("H4").Copy Range("H" & Target.Row)
Range("I4").Copy Range("I" & Target.Row)
Range("H" & Target.Row) = ""
Range("I" & Target.Row) = ""