Hi. 

I have a shared workbook that is used by two processors. On column A I have 
a drop down that allows the processors to select Yes or No. I would like the 
cell that they update to automatically lock by not allowing the processors to 
go back and change what they already selected. If they try to change the 
info, I would like it to prompt a password request in order to make a change 
on that cell. If this is possible please let me know. 

Thanks

Sheet event code..........to be pasted into the sheet module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo enditall
    Application.EnableEvents = False
    If Target.Cells.Column = 1 Then
        With Me
            .Unprotect Password:="justme"  'password to suit
            n = Target.Row
            If .Range("A" & n).Value <> "" Then
                .Range("A" & n).Locked = True  'adjust the "A" if you wanted
'column B locked
            End If
        End With
    End If
enditall:
    Application.EnableEvents = True
    Me.Protect Password:="justme"
End Sub

When happy with code, hit Tools>VBAProject Properties and protect the code
from view.

Alt + q to return to Excel.


Gord Dibben  MS Excel MVP

On Wed, 5 May 2010 11:54:01 -0700, Oscar <Oscar@discussions.microsoft.com>
wrote:

>Hi. 
>
>I have a shared workbook that is used by two processors. On column A I have 
>a drop down that allows the processors to select Yes or No. I would like the 
>cell that they update to automatically lock by not allowing the processors to 
>go back and change what they already selected. If they try to change the 
>info, I would like it to prompt a password request in order to make a change 
>on that cell. If this is possible please let me know. 
>
>Thanks

Thanks for the quick response. I used the code and it works; however, I 
noticed that when I try to update the next row on column A or any other field 
outside of column A, it ask me to unprotect the worksheet using the password. 
Would it be possible to only ask to unprotect the sheet if I tried to go back 
and delete or update a row I already updated under column A?

Thanks

"Gord Dibben" wrote:

> Sheet event code..........to be pasted into the sheet module.
> 
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>     On Error GoTo enditall
>     Application.EnableEvents = False
>     If Target.Cells.Column = 1 Then
>         With Me
>             .Unprotect Password:="justme"  'password to suit
>             n = Target.Row
>             If .Range("A" & n).Value <> "" Then
>                 .Range("A" & n).Locked = True  'adjust the "A" if you wanted
> 'column B locked
>             End If
>         End With
>     End If
> enditall:
>     Application.EnableEvents = True
>     Me.Protect Password:="justme"
> End Sub
> 
> When happy with code, hit Tools>VBAProject Properties and protect the code
> from view.
> 
> Alt + q to return to Excel.
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Wed, 5 May 2010 11:54:01 -0700, Oscar <Oscar@discussions.microsoft.com>
> wrote:
> 
> >Hi. 
> >
> >I have a shared workbook that is used by two processors. On column A I have 
> >a drop down that allows the processors to select Yes or No. I would like the 
> >cell that they update to automatically lock by not allowing the processors to 
> >go back and change what they already selected. If they try to change the 
> >info, I would like it to prompt a password request in order to make a change 
> >on that cell. If this is possible please let me know. 
> >
> >Thanks
> 
> .
> 

I guess I assumed you would Unlock ALL cells on the worksheet prior to
adding the code.

Apologies for not pointing that out.

If you did not then go back and start again.

Unprotect the sheet.

Select all cells and Format>Cells>Protection......uncheck Locked.

Now add the code.

As users select from yes/no dropdown that cell will become locked.

To edit you must enter password to unprotect.


Gord



On Thu, 6 May 2010 13:08:01 -0700, Oscar <Oscar@discussions.microsoft.com>
wrote:

>Thanks for the quick response. I used the code and it works; however, I 
>noticed that when I try to update the next row on column A or any other field 
>outside of column A, it ask me to unprotect the worksheet using the password. 
>Would it be possible to only ask to unprotect the sheet if I tried to go back 
>and delete or update a row I already updated under column A?
>
>Thanks
>
>"Gord Dibben" wrote:
>
>> Sheet event code..........to be pasted into the sheet module.
>> 
>> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>>     On Error GoTo enditall
>>     Application.EnableEvents = False
>>     If Target.Cells.Column = 1 Then
>>         With Me
>>             .Unprotect Password:="justme"  'password to suit
>>             n = Target.Row
>>             If .Range("A" & n).Value <> "" Then
>>                 .Range("A" & n).Locked = True  'adjust the "A" if you wanted
>> 'column B locked
>>             End If
>>         End With
>>     End If
>> enditall:
>>     Application.EnableEvents = True
>>     Me.Protect Password:="justme"
>> End Sub
>> 
>> When happy with code, hit Tools>VBAProject Properties and protect the code
>> from view.
>> 
>> Alt + q to return to Excel.
>> 
>> 
>> Gord Dibben  MS Excel MVP
>> 
>> On Wed, 5 May 2010 11:54:01 -0700, Oscar <Oscar@discussions.microsoft.com>
>> wrote:
>> 
>> >Hi. 
>> >
>> >I have a shared workbook that is used by two processors. On column A I have 
>> >a drop down that allows the processors to select Yes or No. I would like the 
>> >cell that they update to automatically lock by not allowing the processors to 
>> >go back and change what they already selected. If they try to change the 
>> >info, I would like it to prompt a password request in order to make a change 
>> >on that cell. If this is possible please let me know. 
>> >
>> >Thanks
>> 
>> .
>>