code and locked cells

twl2009

Board Regular
Joined
Jan 7, 2016
Messages
247
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
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834
Before making any changes, temporarily unlock cells - and then lock cells again.
Code:
Sub D()
    Sheets("SHEET_NAME").Unprotect Password:="YOUR_PASSWORD"
    ' YOUR CODE HERE
    Sheets("SHEET_NAME").Protect Password:="YOUR_PASSWORD"
End Sub
 

twl2009

Board Regular
Joined
Jan 7, 2016
Messages
247
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
 

Sektor

Well-known Member
Joined
May 6, 2011
Messages
2,834

ADVERTISEMENT

hi.gif
 

twl2009

Board Regular
Joined
Jan 7, 2016
Messages
247
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

Well-known Member
Joined
May 6, 2011
Messages
2,834

ADVERTISEMENT

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

twl2009

Board Regular
Joined
Jan 7, 2016
Messages
247
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top