Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / December 2005

Tip: Looking for answers? Try searching our database.

Locking cels using a UDF

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Viral - 16 Dec 2005 14:21 GMT
I'm not entirely sure if this is possible or not... but here we go.

I'm looking to lock down two cells in a row after data is input into the
cells... for the entire spreadsheet... or at least 2000 rows.  I think the
easiest way would be to have a function since I could fill down and the two
cells would change relative to the position of the function.

Basically, I'm looking to lock cells A1:B1 and so on all the way down the
spreadsheet.  I've seen some subroutines to lock cells, but I don't know how
to use them in the spreadsheet, and I'm having a heck of a time trying to get
this to work.

I'd appreciate any help. Thanks.
Bob Phillips - 16 Dec 2005 14:32 GMT
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.

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> 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 - 16 Dec 2005 14:43 GMT
Awesome, Thanks a million man.  Really needed to know how to use the
subroutines, thanks again.

>Can't do it with a function, but with event code
>
[quoted text clipped - 25 lines]
>>
>> I'd appreciate any help. Thanks.
Gord Dibben - 16 Dec 2005 17:14 GMT
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.
Viral - 16 Dec 2005 21:39 GMT
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!!

>Viral
>
[quoted text clipped - 9 lines]
>>>>
>>>> I'd appreciate any help. Thanks.
Gord Dibben - 16 Dec 2005 23:21 GMT
Haven't tested your code, but looks impressive and you say it does the job.

Goodonya.

Gord

>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
[quoted text clipped - 52 lines]
>>>>>
>>>>> I'd appreciate any help. Thanks.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.