If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Locking cels using a UDF
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. |
#2
|
|||
|
|||
Locking cels using a UDF
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. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Viral" u16718@uwe wrote in message news:58ec62eb8a08e@uwe... 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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
Locking cels using a UDF
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. |
#5
|
|||
|
|||
Locking cels using a UDF
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 |
#6
|
|||
|
|||
Locking cels using a UDF
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Record Locking Problem | KenDunne | General Discussion | 4 | November 11th, 2005 02:54 PM |
Folder locking vrs file locking ? | RVF | General Discussions | 1 | November 2nd, 2005 01:27 PM |
Multi-user locking | Amanda Woodward | General Discussion | 10 | July 1st, 2005 07:20 PM |
Repost: Form does not release locks with record-level locking | david epsom dot com dot au | General Discussion | 2 | December 13th, 2004 01:18 AM |
Problems with record locking | Ronny Salomonsen | General Discussion | 0 | June 14th, 2004 09:37 AM |