worksheet change event (protection issue)

JKCC used Ask the Experts™
Hi Guys,

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) = ""
        End If
    End If
End Sub
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You can change your formula to something like this for row 4

=if(A4="","",<put your original formula here>)

and then copy it down far enough and then protect the sheet.

It will always display blank where column A is blank.


Yes, my formula is already =IF(A4="","",G4*$H$2/1). however the worksheet change event is not working when the sheet is protected. I am using a macro to copy the data down due to the many errors resulting from allowing users access. I now need to limit the access and prevent users from any access to these columns. we are controlling the formula from another sheet in another book.
Top Expert 2010

If you are protecting the worksheet, and you want to update values in locked cells in your Change macro, then you MUST disable worksheet protection whilst you do your updating, and then turn protection back on.

What, exactly, is the problem with that?

My point is that why do you want to copy the formula whenever there is a value in column A? Why not let the formula remain there? No need for any macro


I was hoping that I wouldn't have to do that and there was a workaround that was possible.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial