Forum Discussion
Lock worksheet cell
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 Thank You so much. The code that says Sheet protection for all tables in the workbook is the coding that seems the most simple and easy. I am sending you the workbook please show me to implement that coding, You will see on the workbook Product Description is a drop down list to select from and then the Unit Price get populated with the selected product from Unit Price worksheet.
You have a dropdown menu in your inserted file,
if you lock this as a whole workbook, it will also be locked,
which I think cannot meet your requirements.
Enclosed your file was blocked in January with leaf protection so that the dropdown lists remain free.
Look at January
Thank you for your understanding and patience
Wish you a nice day.
Nikolino
NikolinoDE Hi SORRY i was under the impression i have disabled the worksheet protection. I am sending the workbook to you again.
Thank You
Regards
Please see the month of January.
Is that what you need If so, copy the January VBA code into each individual sheet in all worksheets.
Hope this will help
Thank you for your patience and understanding
Nikolino
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)
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 SubI 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