Forum Discussion

kobus1305's avatar
kobus1305
Brass Contributor
Nov 24, 2020
Solved

Lock worksheet cell

Hi, I have locked cells in my worksheet and all works well. But i have a macro/vba code that fills a cost value in certain cells. It all works perfectly if the cells is not locked as soon as i lock t...
excel
  • NikolinoDE's avatar
    NikolinoDE
    Nov 24, 2020

    kobus1305 

    With the permission of all involved, I would like to add these examples as a very good suggestion from Mr. Hans Vogelaar.

     

    Sheet protection macro without password

    The following macros are used to set and remove sheet protection for Sheet1 without a password.

    Sub sheet protection_on ()

        Sheets ("Table1"). Protect

    End Sub

     

    Sub sheet protection off ()

        Sheets ("Table1"). Unprotect

    End Sub

    --------------------------------------------------

    Macro for sheet protection with password

    The sheet protection can also be set with a password. In the following example, the password is "myPassword".

    Sub Blattschutz_on_with_Passwort ()

        Sheets ("Table1"). Protect Password: = "myPassword"

    End Sub

     

    Sub Sheetprotect_off_with_Passwort ()

        Sheets ("Table1"). Unprotect Password: = "myPassword"

    End Sub

    ------------------------------------------------------

    Sheet protection for all tables in the workbook

     

    A for loop is used to activate or deactivate sheet protection for all tables in a workbook. The following example uses sheet protection without a password.

    Sub Sheetprotect_on_all_Sheets ()

        For Each sheet In ActiveWorkbook.Worksheets

            sheet.Protect

        Next sheet

    End Sub

     

    Sub Sheetprotect_off_all_Sheets ()

        For Each sheet In ActiveWorkbook.Worksheets

            sheet.Unprotect

        Next sheet

    End Sub

    ---------------------------------------------------

    Thank you for your understanding and patience

     

    Wish all a nice day.

     

    Nikolino

    I know I don't know anything (Socrates)

     

     

NikolinoDE's avatar
NikolinoDE
Platinum Contributor
Nov 25, 2020

kobus1305 

Please try it out, it should work.

I actually only built the protection into your macro in the workbook.

 

at the beginning of the macro

Me.Unprotect ("1234")

 

and at the end of the macro

Me.Protect ("1234")

 

With my Office versions 2013 and 2016 it works without problems.

 

Thank you for your patience and time.

 

Nikolino

I know I don't know anything (Socrates)

kobus1305's avatar
kobus1305
Brass Contributor
Nov 27, 2020

NikolinoDE Hi I do not know what it is but it do not work. I have Windows 7 Pro and Office 2013 Pro. Here is the code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'
'Insert price for selected product
'
If Target.CountLarge > 1 Then Exit Sub
Dim r As Long
Dim c As Long
'
Me.Unprotect ("Largo123")
r = Target.Row
c = Target.Column
On Error GoTo Skip
If VBA.Trim(LCase(Sh.Cells(2, c).Value)) = "product description" Then
If Target <> "" Then
Application.EnableEvents = False
With Target.Offset(0, -2)
.FormulaR1C1 = "=VLOOKUP(RC[2],'Product List'!R3C1:R20C3,3,FALSE)"
.Value = .Value
End With
Else
Target.Offset(0, -2) = 0
End If
End If
'
Skip:
Application.EnableEvents = True
Me.Protect ("Largo123")
End Sub

I have checked the execution line by line if the worksheet protection is on it bombs out at the vlookup line but if you disable the worksheet protection it works perfectly. Is there any setting or something that i have to change at my end?

Regards

Thank You