code and locked cells

[twl2009]

I have locked a number of cells on my worksheet, unfortunately it causes the workbook to crash when some code runs.

to put it simply., B2 is a date which is locked and the sheet is protected. When i click 'button1' it runs a bit of code which moves the date in B2 on by a month. Excel doesn't seem to like this and I get
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'}</style>

Run-time error '1004':


Is there a way round this

Thanks

 

[Sektor]

Before making any changes, temporarily unlock cells - and then lock cells again.

Sub D()
    Sheets("SHEET_NAME").Unprotect Password:="YOUR_PASSWORD"
    ' YOUR CODE HERE
    Sheets("SHEET_NAME").Protect Password:="YOUR_PASSWORD"
End Sub

 

[twl2009]

Also this is this the rest of the error message



<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px 'Helvetica Neue'; min-height: 12.0px}</style>The cell or chart that you are trying to change is protected and therefore read-only.


To modify a protected cell or chart, first remove protection using the Unprotect Sheet command (Review tab, Changes group). You may be prompted for a password.



Thanks

 

[twl2009]

Of course! Thank you. I feel really stupid now.

Twl

 

[Sektor]

 

[twl2009]

Feel even more foolish now as i cant get it to work. its in a button click sub, as below

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>'Sub Button2()


'Workheets("Monthly").Unprotect
'Range("B2").Value = DateAdd("M", 1, Range("B2"))
'Sheets("Monthly").Protect


'ActiveSheet.Range("S2").Select


'End Sub

 

[Sektor]

Is "ActiveSheet" a "Monthly" sheet? Anyway, the protection line should be the last one.

 

[twl2009]

Ive taken the active sheet line out altogether and it still doesnt like it. Weirdly the other button Ive used it on works fine.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff}p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; background-color: #ffffff; min-height: 13.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #000000; background-color: #ffffff; min-height: 13.0px}p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; font: 11.0px Menlo; color: #011993; background-color: #ffffff}span.s1 {color: #011993}span.s2 {color: #000000}</style>
'Sub Button1()


Worksheets("Monthly").Unprotect
If Range("B2") > DateSerial(2016, 12, 31) Then

Range("B2").Value = DateAdd("M", -1, Range("B2"))

End If
Worksheets("Monthly").Protect


ActiveSheet.Range("S2").Select




End Sub

 

[twl2009]

Not sure what I did but it all works fine now. Thanks