Unprotect specific cells only when a hidden row was shown by clicking the checkbox

[kares]

Is it possible to unprotect only specific cells in a row when clicking a checkbox which function is to hide/unhide specific rows in a protected sheet?

I have a protected sheet with checkboxes that will hide/unhide specific rows. I have it locked so that no one will do any changes in the entire sheet but rather just have them input necessarily data only to specific cells.

Here is what I would like to happen, instead of unprotecting the whole sheet when the checkbox was ticked and the hidden rows were shown, I want to make the sheet still proctected and hidden rows will still be shown when the checkbox were ticked but only certain cells will be unlocked and will be allowed for edit.

Hope someone can help me with this one. Thank you.

 

[bobsan42]

AFAIK, You cannot unlock cells on a protected sheet. This is the whole point.
the process is: unprotect sheet - unlock cells - protect sheet.

 

[Georgiboy]

Within the 'ThisWorkbook' module, you could protect the sheet in question when the workbook is opened but with the user interface only option as below:

Private Sub Workbook_Open()
    Sheets("Sheet1").Protect , , , , 1
End Sub

Then the VBA on that sheet will be able to run whether the sheet is protected or not. Worth noting though that if the sheet is manually unproteced and reprotected then you would need to reapply the user interface only option with VBA.

As for unlocking specific cells, this could be done manually by right clicking the range then go to format cells then go to the protection tab.

If you really wanted to unlock cells with VBA then the below would be the code:

Range("A3:A5").Locked = False

 

[kares]

Within the 'ThisWorkbook' module, you could protect the sheet in question when the workbook is opened but with the user interface only option as below:

Private Sub Workbook_Open()
    Sheets("Sheet1").Protect , , , , 1
End Sub

Then the VBA on that sheet will be able to run whether the sheet is protected or not. Worth noting though that if the sheet is manually unproteced and reprotected then you would need to reapply the user interface only option with VBA.

As for unlocking specific cells, this could be done manually by right clicking the range then go to format cells then go to the protection tab.

If you really wanted to unlock cells with VBA then the below would be the code:

Range("A3:A5").Locked = False

I'll try this one out and give you feedback if it worked. Thank you.

 

[kares]

The scenario here is that when I tick the checkbox, I want to hide/unhide Rows 2:3 while the sheet is still protected.

Its main purpose is to let users know where to input data depending on its category.

This is the flow of my ongoing project:

Cell A1 refers to the category
If the user ticked the checkbox in B1, then Rows 2:3 will be shown while the sheet is still protected, letting users know that they have to input data into the cells in those rows

But, if B1=False
Then Rows 2:3 should be kept hidden while the sheet is still protected

The code I am using now applies only when the sheet is UNPROTECTED. Here is the code:

Sub CheckBox1_Click()
If ThisWorkbook.Sheets("Sheet 1").CheckBoxes("Check Box 1").Value = 1 Then
ThisWorkbook.Sheets("Sheet 1").Rows("2:3").Hidden = False
Else
ThisWorkbook.Sheets("Sheet 1").Rows("2:3").Hidden = True
End If
End Sub

Also, about unlocking specific cells in a row, I opted to manually set the protection of each cell to focus more on the big problem of mine, which is the above-mentioned scenario

It would be nice if the code could be used whether the sheet is PROTECTED OR NOT.

Hope you can help me with this one.

Thank you.

 

[Georgiboy]

If you put the 'Workbook_Open' sub I posted earlier into the 'ThisWorkbook' module, then save the workbook, then reopen the workbook. Your code should run whether the sheet is protected or not.

OR

You can edit your code above to:

Sub CheckBox1_Click()
    Sheets("Sheet 1").Unprotect
    If Sheets("Sheet 1").CheckBoxes("Check Box 1").Value = 1 Then
        Sheets("Sheet 1").Rows("2:3").Hidden = False
    Else
        Sheets("Sheet 1").Rows("2:3").Hidden = True
    End If
    Sheets("Sheet 1").Protect
End Sub