Lock worksheet cell

[kobus1305]

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 these cells the cost value do not appear in these cells, why and what must i do?????????

Thank You

Regards

[#2]

@kobus1305 

You can unprotect the worksheet at the beginning of your macro, and protect it again at the end of the macro.

[#3 · Accepted solution]

@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)

[#4]

@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.

[#5]

I was happy to help you. I wish you continued success with Excel (the coolest invention since chocola ... uh ... Microsoft! :-))) And… Please keep asking here - I just taught myself Excel with the help of this forum… nearly :)) Nikolino I know I don't know anything (Socrates)

[#6]

@NikolinoDE You never attached the altered worksheet??

Thank You

[#7]

@kobus1305 

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

[#8]

@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

[#9]

@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)

[#10]

@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

[#11]

@kobus1305 

You automatically renew data in certain areas / columns / cells, you have to unlock this area / column / cells with the cell formatting before you lock the workbook.

Otherwise these columns / cells or computation cannot change with the content because they are locked.

Here are the instructions:

Lock or unlock specific areas of a protected worksheet

https://support.microsoft.com/en-us/office/lock-or-unlock-specific-areas-of-a-protected-worksheet-75...

The file I sent you earlier, didn't it work?

Thank you for your patience and time.

Nikolino

I know I don't know anything (Socrates)

[#12]

Not even 1 'like' with Excel.

[#13]

@kobus1305 

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