How to make a locked cell auto update

Hello everyone, this is my very first time posting.

I have the following code which is set up, basicly I want to have cell O2 auto update when I click the button I have added.

It works fine if I do not lock the cell, but if I don't lock the cell it clears it back to blank as I have all unlocked cell being cleared as well.
Any help would be greatly appreceated.

Thanks

mclark15


Cell O:2 is set up for auto invoice number and I also have code there for clearing unlocked cells. If I lock cell O:2 it does not work.
Is there a way to lock this cell and still get the auto numbering code to work.


Sub SaveAs()
Const Variable1 As String = "22032011"
Application.Dialogs(xlDialogSaveAs).Show "Report" & Variable1 & ".xls"
Range("O2").Value = Range("O2").Value + 1
Dim ws As Worksheet
Dim xshape As Shape
For Each ws In ThisWorkbook.Worksheets
For Each xshape In ws.Shapes
If xshape.Type = msoFormControl Then
xshape.ControlFormat.Value = False
End If
Next
Next
Dim c As Range
For Each c In Sheets("FrameChecklist").UsedRange
If c.Locked = False Then
c.Value = ""
End If
Next
End Sub

Hi mclark15,

Welcome to the forum. When posting code please use code tags. It maintains formatting and makes code easier to read. To use code highlight your code and press the # in the toolbar. You might try:

Please Login or Register  to view this content.

My mistake I put a zero in place of the letter O. Try:

Please Login or Register  to view this content.

On your second question, it might look something like this:

Please Login or Register  to view this content.

Well lets step back and figure out exactly what you're trying to do. I thought this was two different issues.

Why and how are you locking sells? When and why do you want to clear the contents?

Try this:

Please Login or Register  to view this content.

Don't manually lock or protect the sheet. It will protect itself automatically every time it is activated but allow for changes to made through code only.

Please Login or Register  to view this content.

Glad to help. And thank you for marking the post as solved.

On any thread you can show your appreciation to those who have helped you by clicking the * below their post.