Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Marking unlocked cells in active worksheet
Hi u all!
I have a rather large spreadsheet taking care of our soccer betting club, and I need to clear the contains of every cell that is not locked when the spreadsheet is locked. Is this possible? I tried this simple function in the EDIT/GOTO...but it seems not to do the job. May be I use it wrong, but I couldn't make it work. I tried all possibilities there I guess. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- |
#2
|
|||
|
|||
Zadig, here is one way with a macro
Sub Delete_Unlocked_Cells() Dim rngeCell As Range Application.ScreenUpdating = False For Each rngeCell In ActiveSheet.UsedRange.Cells If rngeCell.Locked = False Then rngeCell.ClearContents Next Application.ScreenUpdating = True End Sub -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Zadig Galbaras" wrote in message ... Hi u all! I have a rather large spreadsheet taking care of our soccer betting club, and I need to clear the contains of every cell that is not locked when the spreadsheet is locked. Is this possible? I tried this simple function in the EDIT/GOTO...but it seems not to do the job. May be I use it wrong, but I couldn't make it work. I tried all possibilities there I guess. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- |
#3
|
|||
|
|||
Good evening Zadig Galbaras The code below will select all unlocked cells in a given range, making it very easy for you to clear - just press delete. However this will not work with the spreadsheet protected. An easy way around this would be to make the first line unprotect the sheet and the last line protect it again. I don't know how you have protected your sheet (ie password, other settings) so I can only give a little guidance on these commands, but at their most basic would be: ActiveSheet.Unprotect and ActiveSheet.Protect Sub Macro1() Count = 0 On Error Resume Next For Each rng In Selection If rng.Locked = False Then Count = Count + 1 If Count = 1 Then Set Unlocked = rng If Count < 1 Then Set Unlocked = Union(Unlocked, rng) End If Next rng Unlocked.Select End Sub HTH DominicB -- dominicb ------------------------------------------------------------------------ dominicb's Profile: http://www.excelforum.com/member.php...o&userid=18932 View this thread: http://www.excelforum.com/showthread...hreadid=395497 |
#4
|
|||
|
|||
Hi Paul & Dominic
Worked as a charm. Thank you to you both! -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- "Zadig Galbaras" skrev i melding ... Hi u all! I have a rather large spreadsheet taking care of our soccer betting club, and I need to clear the contains of every cell that is not locked when the spreadsheet is locked. Is this possible? I tried this simple function in the EDIT/GOTO...but it seems not to do the job. May be I use it wrong, but I couldn't make it work. I tried all possibilities there I guess. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- |
#5
|
|||
|
|||
Your Welcome
-- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Zadig Galbaras" wrote in message ... Hi Paul & Dominic Worked as a charm. Thank you to you both! -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- "Zadig Galbaras" skrev i melding ... Hi u all! I have a rather large spreadsheet taking care of our soccer betting club, and I need to clear the contains of every cell that is not locked when the spreadsheet is locked. Is this possible? I tried this simple function in the EDIT/GOTO...but it seems not to do the job. May be I use it wrong, but I couldn't make it work. I tried all possibilities there I guess. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can specific cells on a worksheet be hidden & password protected? | Excel Worksheet Functions | |||
move between unlocked cells on protected sheet | Excel Discussion (Misc queries) | |||
Problem trying to move between cells on a protected worksheet. | Excel Discussion (Misc queries) | |||
automatically PRINT only active cells? | Excel Discussion (Misc queries) | |||
How can I merge unlocked cells in a worksheet that has been protec | Excel Discussion (Misc queries) |