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

Gops · Apr 29th 2004

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
wsSheet.Activate
Range("$A$3:$K$84").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
wsSheet.Protect password:=strMasterPassword
Next wsSheet
On Error GoTo 0
End Sub

Anonymous · Apr 29th 2004

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.

Code

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

Display More

yjoshi · Apr 29th 2004

Try following code:

Code

Sub LockAllSheets1()
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
    With wsSheet.Range("$A$3:$K$84")
        .Formula = .Value
        .Locked = True
        .FormulaHidden = False
    End With
    wsSheet.Protect Password:=strMasterPassword
    If wsSheet.Name = Me.Name Then Exit Sub
Next wsSheet
On Error GoTo 0
End Sub

Display More

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.

HTH

Gops · Apr 29th 2004

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.

Gops

Gops · Apr 29th 2004

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.

Anonymous · Apr 29th 2004

Try the following to include deleting the extra sheets.

Code

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 &gt; k Then
                .Delete
            Else
                .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
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

Display More

Gops · Apr 29th 2004

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?

Thanks

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
.Delete
Else
.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

Anonymous · Apr 29th 2004

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.

Gops · Apr 29th 2004

Hi Derk:

It works.

Thanks for all your assistance.

That was fast and brilliant.

Gops

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

Participate now!

Share