Locking cels using a UDF

Awesome, Thanks a million man. Really needed to know how to use the
subroutines, thanks again.

Bob Phillips wrote:
>Can't do it with a function, but with event code
>
>Option Explicit
>
>Private Sub Worksheet_Change(ByVal Target As Range)
>Const WS_RANGE As String = "G2:H2000"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> .Locked = True
> End With
> End If
>
>ws_exit:
> Application.EnableEvents = True
>End Sub
>
>'This is worksheet event code, which means that it needs to be
>'placed in the appropriate worksheet code module, not a standard
>'code module. To do this, right-click on the sheet tab, select
>'the View Code option from the menu, and paste the code in.
>
>> I'm not entirely sure if this is possible or not... but here we go.
>>
>[quoted text clipped - 9 lines]
>>
>> I'd appreciate any help. Thanks.

Viral

Just a note here.

The cells will still be editable until you Protect the Worksheet.


Gord Dibben Excel MVP

On Fri, 16 Dec 2005 14:43:48 GMT, "Viral" <u16718@uwe> wrote:

>Awesome, Thanks a million man. Really needed to know how to use the
>subroutines, thanks again.
>
>Bob Phillips wrote:
>>Can't do it with a function, but with event code
>>
>>Option Explicit
>>
>>Private Sub Worksheet_Change(ByVal Target As Range)
>>Const WS_RANGE As String = "G2:H2000"
>>
>> On Error GoTo ws_exit:
>> Application.EnableEvents = False
>> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
>> With Target
>> .Locked = True
>> End With
>> End If
>>
>>ws_exit:
>> Application.EnableEvents = True
>>End Sub
>>
>>'This is worksheet event code, which means that it needs to be
>>'placed in the appropriate worksheet code module, not a standard
>>'code module. To do this, right-click on the sheet tab, select
>>'the View Code option from the menu, and paste the code in.
>>
>>> I'm not entirely sure if this is possible or not... but here we go.
>>>
>>[quoted text clipped - 9 lines]
>>>
>>> I'd appreciate any help. Thanks.

Yep, found that out. Played around with it a bit, but I got it to do what I
wanted finally... I decided I wanted to do the same thing for another set of
cells in two other columns... as well as protect the page so the cells
couldn't be altered after entering data, here's what I have working. Wooga!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A2:B2000"
On Error GoTo ws_exit:
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="1"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
.Locked = True
End With
End If
ActiveSheet.Protect Password:="1"
ActiveSheet.EnableSelection = xlUnlockedCells

ws_exit:
Const WS_RANGER As String = "H2:I2000"
On Error GoTo ws_exit2:
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="1"
If Not Intersect(Target, Me.Range(WS_RANGER)) Is Nothing Then
With Target
.Locked = True
End With
End If
ActiveSheet.Protect Password:="1"
ActiveSheet.EnableSelection = xlUnlockedCells

ws_exit2:
Application.EnableEvents = True
End Sub

Only thing further is to have only the unlocked cells able to be selected
when the program opens... since they seem to be reverting back to editable
once the file is re-opened.

Thanks a bunch!!

Gord Dibben wrote:
>Viral
>
>Just a note here.
>
>The cells will still be editable until you Protect the Worksheet.
>
>Gord Dibben Excel MVP
>
>>Awesome, Thanks a million man. Really needed to know how to use the
>>subroutines, thanks again.
>[quoted text clipped - 28 lines]
>>>>
>>>> I'd appreciate any help. Thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200512/1

Haven't tested your code, but looks impressive and you say it does the job.

Goodonya.


Gord

On Fri, 16 Dec 2005 21:39:50 GMT, "Viral via OfficeKB.com" <u16718@uwe> wrote:

>Yep, found that out. Played around with it a bit, but I got it to do what I
>wanted finally... I decided I wanted to do the same thing for another set of
>cells in two other columns... as well as protect the page so the cells
>couldn't be altered after entering data, here's what I have working. Wooga!
>
>Option Explicit
>
>Private Sub Worksheet_Change(ByVal Target As Range)
>Const WS_RANGE As String = "A2:B2000"
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> ActiveSheet.Unprotect Password:="1"
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> .Locked = True
> End With
> End If
> ActiveSheet.Protect Password:="1"
> ActiveSheet.EnableSelection = xlUnlockedCells
>
>ws_exit:
> Const WS_RANGER As String = "H2:I2000"
> On Error GoTo ws_exit2:
> Application.EnableEvents = False
> ActiveSheet.Unprotect Password:="1"
> If Not Intersect(Target, Me.Range(WS_RANGER)) Is Nothing Then
> With Target
> .Locked = True
> End With
> End If
> ActiveSheet.Protect Password:="1"
> ActiveSheet.EnableSelection = xlUnlockedCells
>
>ws_exit2:
> Application.EnableEvents = True
>End Sub
>
>Only thing further is to have only the unlocked cells able to be selected
>when the program opens... since they seem to be reverting back to editable
>once the file is re-opened.
>
>Thanks a bunch!!
>
>Gord Dibben wrote:
>>Viral
>>
>>Just a note here.
>>
>>The cells will still be editable until you Protect the Worksheet.
>>
>>Gord Dibben Excel MVP
>>
>>>Awesome, Thanks a million man. Really needed to know how to use the
>>>subroutines, thanks again.
>>[quoted text clipped - 28 lines]
>>>>>
>>>>> I'd appreciate any help. Thanks.