Check Box Issue

[sofiavba]

Hi Everyone,

Here is my issue. I have a excel work book that has 5 sheets. On one of the sheets there is a checkbox, when the checkbox is checked I need to unprotect cells to allow the users to update it in a different sheet in the workbook. As VBA is new to me, can anyone give me some direction.

Thank you

 

[RonRepp]

You need to get to the code behind the checkbox. This can be done by clicking Edit Code Icon on the Forms Toolbar.

Sub CheckBox2_Click()
ActiveWorkbook.Unprotect
End Sub

or

Sub CheckBox2_Click()
ActiveSheet.Unprotect
End Sub

I hope this helps.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[sofiavba]

This unprotects the full worksheet, I need only one row on the different sheet to be updateable. Meaning, Sheet1 has the checkbox, when the checkbox is checked and the user goes to sheet2 only one row should be updateable, they should not be able to update any other row which was protected before the checkbox was checked.

 

[PHV]

You may try to play with the SelectionChange event of the Worksheet object and the Intersect and Activate (or Select) methods.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vladk]

sofiavba,

Assuming that the "Sheet2" is protected with no password, then the example for some range would be:

Private Sub CheckBox1_Click()

With Worksheets("Sheet2")
.Unprotect
.Range("A1").Locked = Not .Range("A1").Locked
.Protect
End With

End Sub

I am not sure if you can apply this approach to the entire row.

Vladk

 

[vladk]

One row:

Private Sub CheckBox1_Click()

With Worksheets("Sheet1")
.Unprotect
.Rows("1:1").Locked = Not .Rows("1:1").Locked
.Protect
End With

End Sub