# VBA Lock and unlock a sheet.



## Genetu (Dec 14, 2022)

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 (Dec 14, 2022)

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 (Dec 14, 2022)

Jimmypop said:


> Hi there
> 
> Have you tried...
> 
> ...


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 (Dec 14, 2022)

Genetu said:


> 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 (Dec 15, 2022)

Jimmypop said:


> 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 (Dec 15, 2022)

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 (Dec 16, 2022)

Sanjeev1976 said:


> You can try :
> 
> 
> ```
> ...


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 (Dec 16, 2022)

Genetu said:


> 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 (Dec 16, 2022)

```
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 (Dec 16, 2022)

Genetu said:


> 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
```


----------



## Genetu (Dec 14, 2022)

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
```


----------



## Genetu (Dec 16, 2022)

Sanjeev1976 great job the only thing left is unprotecting other cells except for formulas. Check the code; perfectly run the main code after unprotecting the sheet, through "execute code". But other cells could not accept any data. The dialogue box "The cell you are trying to change is protected...".


----------



## Sanjeev1976 (Dec 16, 2022)

Genetu said:


> Sanjeev1976 great job the only thing left is unprotecting other cells except for formulas. Check the code; perfectly run the main code after unprotecting the sheet, through "execute code". But other cells could not accept any data. The dialogue box "The cell you are trying to change is protected...".


You need to tell exactly what you want to do with the other cells. This code unprotects the sheet performs what is mentioned in the Execute Code and then protects the sheet again.

Whatever you need to do should be part of the Execute Code or else you can simply unprotect the sheet and continue with what needs to be done.


----------



## Genetu (Dec 17, 2022)

Sanjeev1976 said:


> You need to tell exactly what you want to do with the other cells. This code unprotects the sheet performs what is mentioned in the Execute Code and then protects the sheet again.
> 
> Whatever you need to do should be part of the Execute Code or else you can simply unprotect the sheet and continue with what needs to be done.


Ok, Sir. What I need is a sheet that has a formula. The formula must not be edited but hidden (not shown on the formula bar). To do that the sheet must be protected. Other cells, except formula cells, should accept and edit any value. In the execute code, if [A1], [A2], and [A4] values were typed on the cell, you can't after running the code twice. But this is done manually but I need to do this in VBA. 
              All formulas are hidden and protected, other cells should accept & edit any data even after running the code many times. This is what I want, Sanjeev1976. Thank you in advance.


----------



## Sanjeev1976 (Dec 17, 2022)

Select the entire sheet and format all the cells except cells [A1], [A2], and [A4] as unlocked and not hidden and only the cells [A1], [A2], and [A4] as locked and hidden. Then you are good to go with the current VBA code


----------

