Forum Discussion
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 these cells the cost value do not appear in these cells, why and what must i do?????????
Thank You
Regards
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)
12 Replies
- Not even 1 'like' with Excel.
- 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)
NikolinoDE You never attached the altered worksheet??
Thank You
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
You can unprotect the worksheet at the beginning of your macro, and protect it again at the end of the macro.
