Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all but protected cells
Hello,
I am trying to put together a macro to delete all but the protected cells. Nothing I try seems to work. Has anyone done this before? It is easy for me to turn protection on and off with a macro so shouldnt it also be easy to delete the content of all non protected cells as well? Thanks in advance Kingtriton --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all but protected cells
King
Sub UnLocked_Cells() Dim cell As Range, tempR As Range, rangeToCheck As Range 'rotate through all cells in the selection For Each cell In Intersect(Selection, ActiveSheet.UsedRange) If Not cell.Locked Then 'add to tempR if unprotected If tempR Is Nothing Then 'do it this way for the first unprotected cell Set tempR = cell Else 'add subsequent cells this way. Set tempR = Union(tempR, cell) End If End If Next cell 'do this upon completion of the For..Next checking If tempR Is Nothing Then MsgBox "There are no UnLocked cells in " & _ "the selected range." End End If 'select qualifying cells tempR.Select Selection.ClearContents End Sub Gord Dibben Excel MVP On Thu, 22 Jan 2004 20:25:24 -0600, Kingtriotn wrote: Hello, I am trying to put together a macro to delete all but the protected cells. Nothing I try seems to work. Has anyone done this before? It is easy for me to turn protection on and off with a macro so shouldnt it also be easy to delete the content of all non protected cells as well? Thanks in advance Kingtriton --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all but protected cells
That clears the contents of all unlocked cells but only if the sheet is
not protected. This is where I keep running into walls, why wont it clear the contents of the unlocked cells when the sheet is protected? Thanks again, Kingtriton --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all but protected cells
Kingtriton,
why wont it clear the contents of the unlocked cells when the sheet is protected? The simple answer is because the sheet is protected. Two options....... If the sheet is manually password protected, unprotect it before you run the code and reprotect it afterwards. or................... Use this code (modify to suit) in the Workbook_Open event: Worksheets("yoursheetname") Protect Password:="yourpassword", _ UserInterfaceOnly:=True The above will protect the worksheet but allow it to be modified via code. It has to be set each time the workbook opens. The "protect" will stick on close/reopen but the "UserInterfaceOnly" won't. John "Kingtriotn " wrote in message ... That clears the contents of all unlocked cells but only if the sheet is not protected. This is where I keep running into walls, why wont it clear the contents of the unlocked cells when the sheet is protected? Thanks again, Kingtriton --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all but protected cells
I dont understand, if the cells are unlocked and the sheet is protected,
you can still manipulate the unlocked cells, only the locked cells are protected when the sheet is protected. I will be distributing this worksheet to a bevy of computer illeterate people who work for me and I need it to be SIMPLE for them to use. Is there no way to clear all of the unprotected cells at once. I could just record a macro and holding control, select every cell that I want to delete, then hit delete, stop recording macro and then link it to a button. I want to avoid this because it makes the sheet move around when you run the sub. Even if I delete all of the scroll lines in the sub, the sheet still appears to have a seizure. Thanks, Kingtriton --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all but protected cells
Kingtriton,
Is there no way to clear all of the unprotected cells at once. I dont understand, if the cells are unlocked and the sheet is protected, you can still manipulate the unlocked cells, only the locked cells are protected when the sheet is protected. Yes, you can manipulate the unlocked cells but you can't change the properties of those cells. Example: Take a sheet and unlock one cell. Protect the sheet and then select "Format" Note that the "Cells" option is grayed out. The "Format" option is worksheet dependent. Excel hasn't evaluated what cell you're in at this point. It only knows that the sheet is protected and you can't choose that particular option. Is there no way to clear all of the unprotected cells at once. Gord's code will do this with my suggestions to either protect the sheet via code (with the UserInterface option) or Unprotect the sheet before you run Gord's code (and reprotect afterwards). I want to avoid this because it makes the sheet move around when you run the sub Application.ScreenUpdating = False at the beginning of your code will cure this. John "Kingtriotn " wrote in message ... I dont understand, if the cells are unlocked and the sheet is protected, you can still manipulate the unlocked cells, only the locked cells are protected when the sheet is protected. I will be distributing this worksheet to a bevy of computer illeterate people who work for me and I need it to be SIMPLE for them to use. I could just record a macro and holding control, select every cell that I want to delete, then hit delete, stop recording macro and then link it to a button. I want to avoid this because it makes the sheet move around when you run the sub. Even if I delete all of the scroll lines in the sub, the sheet still appears to have a seizure. Thanks, Kingtriton --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all but protected cells
King
Macro works for me on a protected sheet. Hit CRTL + A to select all cells then run it. Clears the contents of all "unlocked" cells. Gord Dibben Excel MVP On Thu, 22 Jan 2004 21:24:08 -0600, Kingtriotn wrote: That clears the contents of all unlocked cells but only if the sheet is not protected. This is where I keep running into walls, why wont it clear the contents of the unlocked cells when the sheet is protected? Thanks again, Kingtriton --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete all but protected cells
Tested in XL 97 and XL 2002
Gord On Fri, 23 Jan 2004 10:42:14 -0800, Gord Dibben <gorddibbATshawDOTca wrote: King Macro works for me on a protected sheet. Hit CRTL + A to select all cells then run it. Clears the contents of all "unlocked" cells. Gord Dibben Excel MVP On Thu, 22 Jan 2004 21:24:08 -0600, Kingtriotn wrote: That clears the contents of all unlocked cells but only if the sheet is not protected. This is where I keep running into walls, why wont it clear the contents of the unlocked cells when the sheet is protected? Thanks again, Kingtriton --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protected sheet - attempt to delete row = unmerged cells | Excel Discussion (Misc queries) | |||
Delete unprotected cells in protected worksheet | Excel Worksheet Functions | |||
How do I delete only the content of cells that are NOT protected? | Excel Discussion (Misc queries) | |||
How to delete the content of all unlocked cells in a protected she | Excel Discussion (Misc queries) | |||
How do you delete one cell from a range of protected cells | Excel Worksheet Functions |