[Solved] VBA : How to lock (convert formulae into numbers) &

  • Need your help, please:

    The following code works OK to "lock" all sheets in the workbook. For example, if there are 5 sheets and if I triger this code while in sheet 4, it locks all sheets including sheet 5. If there is no sheet 5, it locks all sheets up to 4. However, it is possible to have sheets more than the sheets you wish to "lock".

    What I need is to lock only up to sheet 4 and not sheet 5. In other words, could you suggest a modification to the code so that it locks all sheets including the sheet the user is in and NOT the sheets higher.

    Sub LockAllSheets()
    If strMasterPassword = "" Then
    strMasterPassword = Worksheets(1).Range("E100").Value
    End If
    Dim wsSheet As Worksheet
    On Error Resume Next
    For Each wsSheet In Worksheets
    wsSheet.Unprotect password:=strMasterPassword
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Selection.Locked = True
    Selection.FormulaHidden = False
    wsSheet.Protect password:=strMasterPassword
    Next wsSheet
    On Error GoTo 0
    End Sub

  • Try the following. It assumes the sheets in your workbook up to the activesheet are worksheets. I also eliminated the need to select and copy.

  • Try following code:

    The difference from Derk's approach is that i am using Me keyword which will mean, the sheet in which the code resides will be used.

    Also looks like you want to lock only the specific range and not all the cells, so i have put the code for locking, hiding formulae in the same with clause.

    It did work for me perfectly.


    Thanks: ~Yogendra

  • Thanks Derk:

    That was super fast.

    Your code not only works but also elegant.

    Relating to the same code:

    Is there a way to delete the worksheets higher than the activesheet, if any (sometimes there may not be any). Because the sheets higher than the active sheet are not used by the user for this task.


  • Thanks Yjoshi:

    Actually the code resides in the "workbook" not in the sheet. Thanks for your response, though.

    Hi DerK; Re: my delete question, note that the workbook is protected with the same password as the worksheet.

  • Try the following to include deleting the extra sheets.

  • Hi Derk:

    Deleting did not work. That is because, the workbook is also protected (with the same password as the worksheet).

    I have added the "unprotect" and "protect" code lines as shown below:

    Can you pl. conform if it is OK?


    Sub LockAllSheets()
    Dim strMasterPassword As String, k As Integer, w As Worksheet
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    If strMasterPassword = "" Then
    strMasterPassword = Worksheets(1).Range("E100").Value
    End If
    k = ActiveSheet.Index
    On Error Resume Next
    For Each w In Worksheets
    With w
    .Unprotect Password:=strMasterPassword
    If .Index > k Then
    ActiveWorkbook.Unprotect Password:=strMasterPassword 'new line
    .Range("$A$3:$K$84").Value = .Range("$A$3:$K$84").Value
    With .Cells
    .Locked = True
    .FormulaHidden = False
    End With
    .Protect Password:=strMasterPassword
    End If
    End With
    Next w
    ActiveWorkbook.Protect Password:=strMasterPassword 'new line
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    End Sub

  • Looks ok to me, but the proof is whether it works for you. The unprotect workbook line can be put in before the For Each, but if we are only talking about deleting a sheet or two, it doesn't really matter.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!