Protected sheet - deleting unlocked contents

C

carrera

I have a password protected workbook with multiple cells unlocked for users
to fill in data.
Rather than sending a workbook with multiple copies of the same sheet (one
for each month of the year) is there an easy way the user can quickly delete
the contents of the unlocked cells without clicking on each cell, or using
ctrl click?

I am not experienced at all with macros - but I have a feeling one would be
involved.
What would be the steps involved.
Well, I'm outa here for the weekend, talk to youse guys Monday! Ya'll have a
great weekent
BTW - Thanks for all the past help - you're all great!
 
D

Dave Peterson

If you select the cells first and give that selected range a nice name, the
users could select the range and hit the delete key.

Select your cells (click & ctrlclick)
type the name you want in the namebox (to the left of the formulabar)
hit enter.

Have the users hit F5 (edit|goto) and type that name.

It'll select the same cells as you selected.
 
P

Paul B

carrera, give this a try
Will delete info in all unlocked cells

Sub DeleteUnlockedCells()
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 2000 & 2003
** remove news from my email address to reply by email **
 
P

Paul B

Carrera, yes this is a macro, if you want to give it a try first backup your
workbook, just in case, then

To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window click on your workbook name, go to
insert, module, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your workbook
and press alt and F8, this will bring up a box to pick the Macro from, click
on the Macro name to run it. If you are using excel 2000 or newer you may
have to change the macro security settings to get the macro to run.


--
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 2000 & 2003
** remove news from my email address to reply by email **
 
D

Dave Peterson

First, I'd stay away from a name of A1. It looks too much like the address of
the topleftcell. In fact, if you're using A1 reference style, you can't use
that as a name.

And if you're using R1C1 reference style, it'll be a slight problem if you ever
change back to A1 reference style.

My bet is that you had a whole bunch of cells in that range.

You could either make a bunch of names that include a smaller number of cells,
then make one giant range that refers to all of the smaller number of cells
range names.

Let's say you have 12 ranges named clear1,clear2, ...,clear12

Then insert|Name|define
Make a new name (ClearALL??)
but have it refer to:
=clear1,clear2,clear3, ..., clear12

(Type in all those names--I got lazy!)

======
Another option:

Select your cells (click and ctrl-click to get them all.
Then hit alt-F11 (to get to the VBE--where macros live)
then hit ctrl-g (to see the immediate window)

type this and hit enter:
selection.name = "Clear"

Then alt-f11 back to excel.
F5 (aka edit|goto)
Clear
and see if that works (then hit the delete key).




Hi Dave - this worked - Sort Of.
still having a prob. One - you said to give the range a name - I called it
"clear" but it said it was an invalid reference. So, I just call it A1.
Most important though - when I ctrl clicked all the unlocked cells, named it
A1 and test went to F5 to test, it would only highlight part of the range.
If I started at one end, it would only highlight about the first 3rd of the
cells, if I started at the other end of the sheet, it would only highlight
the last third.
Hmmmm, can you help?
Click to expand...
 
Top