Protect or unprotect cells depending on option selected

[General Ledger]

Dear All:

I have an Excel 2003 file that will be available to several users. One worksheet will be a form for the user to fill in and other worksheets will be hidden. The FORM worksheet will be protected so only cells where the user is to enter data will be unprotected.

My problem is I need to turn on cell protection for some cells depending on the option the user selects. For example, if the user selects option #1, some cells (B2 and B3) will be populated with data by a macro and I want to protect those cells from being changed by the user. If the user selects option #2, those cells should remain unprotected so the user may enter data.

Since the worksheet is already protected (to prevent the user from changing other cells), simply turning on and off worksheet protection won't do the job. I need to be able to leave the worksheet protected but alter which cells are protected.

 

[dunk123]

Can you not use the worksheet change event to test for the value used in the selection box and then alter the properties of the cells you need to with code and then reprotect the sheet?

 

[General Ledger]

dunk123,

OK, so what is the VBA to do that?

 

[dunk123]

Something along the lines of this. You have to put this code in the Sheet object in question, not in a Module:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$B$1" Then Exit Sub

If Range("B1") = "Option 1" Then
'put your unprotect sheet, change formatting of cells to be unlocked, and protect sheet code here. This should be recordable

ElseIf Range("B1") = "Option 2" Then
'put your other unprotect sheet, change formatting of cells to be unlocked, and protect sheet code here. This should be recordable

End If

End Sub

Not sure which form you are using and which linked cell, but i have assumed that when they make a selection the value of B1 changes and depending on what this value is triggers the relevant code. Hope this helps you get closer.