Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling Cell Editing
I have a two columns in a worksheet, one holds first names and the other
holds last names. I want to disable the cells after names have been entered to keep a user from recycling cells with new names. Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling Cell Editing
You might try using the below code.
1. Format the sheet with cells unlocked 2. put it into a worksheet change event 3. start the code with this If to capture the column you want to capture the change If target.Column = 2 then 4. restrict the code to work on itself rather than all worksheets. 5. add a line in the code (just before the code re-protects) Target.Row.Locked = True than the row will be protected and the user cannot select it. (you may need to provide an unlock code to make corrections) Also this works in Excel 2000 and may need modifications for later versions. Select UnProtected Cells ONLY This will prevent users from clicking on protected cells on all worksheets. Therefore, the warning message will not appear. The code must be enterred in the ThisWorkbook module. Note that the EnableSelection property must be reset each time the workbook is opened as it defaults to xlNoRestrictions. The worksheet(s) must first be unprotected to set the EnableSelection property and then must be protected for it to take effect. Private Sub Workbook_Open() Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets WS.Unprotect 'Password:="wxyz" WS.EnableSelection = xlUnlockedCells WS.Protect 'Password:="wxyz", UserInterfaceOnly:=True Next End Sub Regards, Greg Wilson 5/3/03 -- steveB Remove "AYN" from email to respond "programmingrookie" wrote in message ... I have a two columns in a worksheet, one holds first names and the other holds last names. I want to disable the cells after names have been entered to keep a user from recycling cells with new names. Is this possible? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling Cell Editing
Further to Steve's comment, I was running xl97 back then (2003). I now have
xl2000 and I note that you don't have to unprotect anymore before changing the EnableSelection property. You still have to reset the EnableSelection property to xlUnlockedCells each time the wb is opened. You should experiment with whatever version you are running. Regards, Greg Wilson "programmingrookie" wrote: I have a two columns in a worksheet, one holds first names and the other holds last names. I want to disable the cells after names have been entered to keep a user from recycling cells with new names. Is this possible? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling Cell Editing
If I had a column, say B5 to B45, that the names are to be entered into,
would this code allow me to disable a portion of the column while the rest remained editable? I would like to disable those with entries while allowing new entries to still be entered in the column. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disabling Cell Editing
This is code from help. It looks to see if one range iscontained within
another range.Just remember that the code is designed to allow the user to only select cells that are not Locked... So you start with all (or most)cells unlocked and than start locking the ones you want protected.replace Range("rg1") with Target Range("rg2") with Range("B2:B45")Than use Target.Locked = trueYou will probably have to unprotect before, and reprotect after.Worksheets("Sheet1").Activate Set isect = Application.Intersect(Range("rg1"), Range("rg2")) If isect Is Nothing Then MsgBox "Ranges do not intersect" Else isect.Select End If -- steveB Remove "AYN" from email to respond "programmingrookie" wrote in message ... If I had a column, say B5 to B45, that the names are to be entered into, would this code allow me to disable a portion of the column while the rest remained editable? I would like to disable those with entries while allowing new entries to still be entered in the column. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Editing in a cell | New Users to Excel | |||
disabling the copy paste function in a cell | Excel Worksheet Functions | |||
Disabling protected cell selection | Excel Worksheet Functions | |||
Disabling Cell References | New Users to Excel | |||
Disabling a cell for editing or deleting | Excel Programming |