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:

Range("02").Locked = False
Range("O2").Value = Range("O2").Value + 1
Range("o2").Locked = True

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

Sub SaveAsThis()

Const Variable1 As String = "22032011"
Dim ws As Worksheet
Dim xshape As Shape
Dim c As Range

Application.Dialogs(xlDialogSaveAs).Show "Report" & Variable1 & ".xls"
Range("O2").Locked = False
Range("O2").Value = Range("O2").Value + 1
Range("O2").Locked = True
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
For Each c In Sheets("FrameChecklist").UsedRange
    If c.Locked = False Then
        c.Value = ""
    End If
Next
End Sub

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

Range("A48,A49,C18,C25,C37,C45,C47").ClearContents

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:

Sub SaveAsThis()

Const Variable1 As String = "22032011"
Dim ws As Worksheet
Dim xshape As Shape
Dim c As Range

Application.Dialogs(xlDialogSaveAs).Show "Report" & Variable1 & ".xls"
Range("O2").Value = Range("O2").Value + 1
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
Range("A48,A49,C18,C25,C37,C45,C47").ClearContents
End Sub

Private Sub Worksheet_Activate()
ActiveSheet.Protect userinterfaceonly:=True
End Sub

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.

Sub SaveAsThis()

Dim ws As Worksheet
Dim strNew As String
Dim strTemp As String
Dim xshape As Shape
Dim c As Range

strTemp = "C:\Users\bhyrj\Desktop\book1.xlsm"  '<---- set where you want the blank template stored
strNew = Application.GetSaveAsFilename & ".xlsx"  '<---- Location to save complete form chosen by dialog box

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs strNew, 51

Range("O2").Value = Range("O2").Value + 1
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
Range("A48,A49,C18,C25,C37,C45,C47").ClearContents
ActiveWorkbook.SaveAs strTemp, 52
Application.DisplayAlerts = True
End Sub

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.