My macro doesn't work if sheet is protected

[papajups]

Hi and Good day!


I would like to seek for your help regarding my concern. I have a macro-enabled file and it contains formula. the formula is in AH3 and it is being copied down up to the last value I have in A. I declared AH3 as locked cell and all the rest are unlocked cells, and I protected the sheet so my formula in AH3 will not be modified or deleted.

My problem is, below simple macro doesn't work anymore

Sub deleteoldbrt()'
' deleteoldbrt Macro
'


'
    Rows("34:34").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A3:AG9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("AH4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
End Sub

What i want to happen is to prevent AH3 from being modified or totally deleted and at the same time I can still use the macro above because details being pasted needs to be remove from time to time.

Please help me figure this one out. Thanks a lot in advance for any help that will be given.

God bless!


best regards,


papajups

 

[VoG]

Try this - change abc to the actual password (twice)

Sub deleteoldbrt() '
' deleteoldbrt Macro
'


'
ActiveSheet.Unprotect Password:="abc"
    Rows("34:34").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Range("A3:AG9").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("AH4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("A1").Select
ActiveSheet.Protect Password:="abc"

End Sub

 

[papajups]

Hi Sir Vog,


Thank you so much for the codes you have given me but i found some bugs in using your codes. After running the macro, range A3:AG33 becomes locked cells and you can't paste anymore in the said range. Also, my formula in AH3 becomes an unlock cell and thus may subject from deletion and modification.

May I know why this happens?

your response will be greatly appreciated.


thanks and best regards,


papajups

 

[VoG]

There is nothing in that code that locks or unlocks cells. It just removes the protection whilst the code runs then applies the protection again. Cells that were locked before the code runs will stay locked and unlocked cells will still be unlocked.

 

[papajups]

Hi Sir,


I still want to thank you for the help you have givenme, for sharing codes and most importantly your knowledge and time . thank you Sir!


best regards,


papajups