VBA Lock and unlock a sheet.

[Genetu]

I have a sheet with a formula and there is a VBA code run on it. The formula should be hidden (protected). When the code is run it can not change the Vloockup value of the cell. The first value is continued till the end. When the formula is unprotected everything is OK. But I need the formula to be hidden. This is the step I trying to fix.

Sub vlookup_Value()
     'Unprotect the sheet and formula

            'Any code .........

    'Protect sheet and cells with the formula
End sub

 

[Jimmypop]

Hi there

Have you tried...

Sub vlookup_Value()
ActiveSheet.Unprotect "Password"     'Unprotect the sheet and formula
      'Any code .........
ActiveSheet.Protect "Password"     'Protect sheet and cells with the formula
End Sub

 

[Genetu]

Hi there

Have you tried...

Sub vlookup_Value()
ActiveSheet.Unprotect "Password"     'Unprotect the sheet and formula
      'Any code .........
ActiveSheet.Protect "Password"     'Protect sheet and cells with the formula
End Sub

Ya, Jimmy. The first execution of code is, ok. but on the second the sheet is surely unprotected and will not work for formulas to be updated. An error message is displayed, "the sheet is protected...".

 

[Jimmypop]

Ya, Jimmy. The first execution of code is, ok. but on the second the sheet is surely unprotected and will not work for formulas to be updated. An error message is displayed, "the sheet is protected...".

Hi there

You will need to unlock the sheet at beginning... have all formulas run and update... and then protect again... maybe give us the entire code you are working with?

 

[Genetu]

Hi there

You will need to unlock the sheet at beginning... have all formulas run and update... and then protect again... maybe give us the entire code you are working with?

I did it as you suggest but I will try it to be sure. If not working I will post the code.

 

[Sanjeev1976]

You can try :

Sub vlookup_Value()
If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect "Password"     'Unprotect the sheet and formula
Else
      'Any code .........
End If
ActiveSheet.Protect "Password"     'Protect sheet and cells with the formula
End Sub

 

[Genetu]

You can try :

Sub vlookup_Value()
If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect "Password"     'Unprotect the sheet and formula
Else
      'Any code .........
End If
ActiveSheet.Protect "Password"     'Protect sheet and cells with the formula
End Sub

Sanjeev, thanks for your help. The code is good to unprotect and protect the sheet. But the middle code will execute once only. On the next execution, the code will be skipped and protect the sheet. I have modified the code as below. The main problem is I can't insert (edit) any value on cells after protecting the formula. The only thing I need is to hide the formula after the code is executed.

Sub vlookup_Value()
If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect "Password"     'Unprotect the sheet and formula
Else
End If
      [A1] = 8
      [A2] = 6
      [A4] = [A1] + [A2]
ActiveSheet.Protect "Password"     'Protect sheet and cells with the formula
End Sub

 

[Genetu]

I did it as you suggest but I will try it to be sure. If not working I will post the code.

Here is the code I can't insert or edit any value in any cell. The only thing I need is to unprotect the sheet before the main code is executed then hide the formulas and protect the sheet.

Sub Macro1()
  ActiveSheet.Unprotect
                    ' The main code [A1] = 18
    [A2] = 36
    [A4] = [A1] + [A2]

    Cells.Select
    Selection.Locked = False
    Selection.Locked = True
    Selection.FormulaHidden = True
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
        AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, _
        AllowInsertingRows:=True, AllowDeletingColumns:=True, _
        AllowSorting:=True
  [A4].Select
End Sub

 

[Sanjeev1976]

Sub vlookup_Value()
If ActiveSheet.ProtectContents = True Then
        ActiveSheet.Unprotect "Password"     'Unprotect the sheet and formula
        Execute_Code
Else
        Execute_Code
End If
ActiveSheet.Protect "Password"     'Protect sheet and cells with the formula
End Sub

Sub Execute_Code()
    [A2] = 36
    [A4] = [A1] + [A2]

    Cells.Select
    Selection.Locked = False
    Selection.Locked = True
    Selection.FormulaHidden = True
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
        AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, _
        AllowInsertingRows:=True, AllowDeletingColumns:=True, _
        AllowSorting:=True
    [A4].Select
End Sub

 

[Genetu]

I did it as you suggest but I will try it to be sure. If not working I will post the code.

Hi Jimmypop. This is the code. But I can't edit and insert any value to cells. Only formulas have been protected and hidden. Thanks for your help.

Sub Vlookup_value()
  ActiveSheet.Unprotect
    
                ' The main code
    [A1] = 18
    [A2] = 36
    [A4] = [A1] + [A2]

    Cells.Select
    Selection.Locked = False
    Selection.Locked = True
    Selection.FormulaHidden = True
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
        AllowFormattingCells:=True, AllowFormattingColumns:=True, _
        AllowFormattingRows:=True, AllowInsertingColumns:=True, _
        AllowInsertingRows:=True, AllowDeletingColumns:=True, _
        AllowSorting:=True
  [A4].Select
End Sub