Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock used cells
I have a spreadsheet that I would like the cells to lock after a value has
been entered. Can I do this with a macro or in the sheet code? Thanks Mike |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock used cells
One way:
Put this in the worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sPWORD As String = "drowssap" Dim rArea As Range Dim rCell As Range Me.Unprotect Password:=sPWORD For Each rArea In Target For Each rCell In rArea With rCell .Locked = Not IsEmpty(.Value) End With Next rCell Next rArea Me.Protect Password:=sPWORD End Sub In article , MIke wrote: I have a spreadsheet that I would like the cells to lock after a value has been entered. Can I do this with a macro or in the sheet code? Thanks Mike |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock used cells
JE,
Thanks. It works great. Mike "JE McGimpsey" wrote: One way: Put this in the worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sPWORD As String = "drowssap" Dim rArea As Range Dim rCell As Range Me.Unprotect Password:=sPWORD For Each rArea In Target For Each rCell In rArea With rCell .Locked = Not IsEmpty(.Value) End With Next rCell Next rArea Me.Protect Password:=sPWORD End Sub In article , MIke wrote: I have a spreadsheet that I would like the cells to lock after a value has been entered. Can I do this with a macro or in the sheet code? Thanks Mike |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock used cells
JE,
Wll these changes be applied to other worksheets if I need to add them? Thanks, Mike "JE McGimpsey" wrote: One way: Put this in the worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sPWORD As String = "drowssap" Dim rArea As Range Dim rCell As Range Me.Unprotect Password:=sPWORD For Each rArea In Target For Each rCell In rArea With rCell .Locked = Not IsEmpty(.Value) End With Next rCell Next rArea Me.Protect Password:=sPWORD End Sub In article , MIke wrote: I have a spreadsheet that I would like the cells to lock after a value has been entered. Can I do this with a macro or in the sheet code? Thanks Mike |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock used cells
No. If you want cells to be locked on *every* sheet in the workbook,
change the macro from the Worksheet_Change event to the Workbook_SheetChange() event in the ThisWorkbook code module. In article , MIke wrote: Wll these changes be applied to other worksheets if I need to add them? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock used cells
If you want the code to run on all sheets, current or added, change the event
type to Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Gord Dibben MS Excel MVP On Fri, 19 Oct 2007 09:30:01 -0700, MIke wrote: JE, Wll these changes be applied to other worksheets if I need to add them? Thanks, Mike "JE McGimpsey" wrote: One way: Put this in the worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sPWORD As String = "drowssap" Dim rArea As Range Dim rCell As Range Me.Unprotect Password:=sPWORD For Each rArea In Target For Each rCell In rArea With rCell .Locked = Not IsEmpty(.Value) End With Next rCell Next rArea Me.Protect Password:=sPWORD End Sub In article , MIke wrote: I have a spreadsheet that I would like the cells to lock after a value has been entered. Can I do this with a macro or in the sheet code? Thanks Mike |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
lock used cells
Missed one part.
Move the code to Thisworkbook module from sheet module. Gord On Fri, 19 Oct 2007 15:13:20 -0700, Gord Dibben <gorddibbATshawDOTca wrote: If you want the code to run on all sheets, current or added, change the event type to Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Gord Dibben MS Excel MVP On Fri, 19 Oct 2007 09:30:01 -0700, MIke wrote: JE, Wll these changes be applied to other worksheets if I need to add them? Thanks, Mike "JE McGimpsey" wrote: One way: Put this in the worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Const sPWORD As String = "drowssap" Dim rArea As Range Dim rCell As Range Me.Unprotect Password:=sPWORD For Each rArea In Target For Each rCell In rArea With rCell .Locked = Not IsEmpty(.Value) End With Next rCell Next rArea Me.Protect Password:=sPWORD End Sub In article , MIke wrote: I have a spreadsheet that I would like the cells to lock after a value has been entered. Can I do this with a macro or in the sheet code? Thanks Mike |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How lock just some cells | New Users to Excel | |||
Lock all cells? | Excel Discussion (Misc queries) | |||
How do I lock all cells in a given row together to one another? | Excel Discussion (Misc queries) | |||
how can I lock certain cells? | Excel Discussion (Misc queries) | |||
Lock Cells | Excel Discussion (Misc queries) |