Perhaps this will offer a solution.
Start by protecting your worksheet again. If you are going to protect it
with a password, write it down exactly as it must be.
Next use Tools | Macro | Record New Macro
give it a nice friendly name like SortStuff (or not)
and go through the exact process you would have to go thru by hand:
start by unprotecting the worksheet, including entering the password if
needed,
then choosing the area to be sorted and then using the Data | Sort function
with whatever choices you need to make, end by hitting [Esc] to unselect the
area and maybe even choosing a convenient cell to end up in, and finally
Protect the sheet again. stop recording the macro.
Now, if you used a password to protect the sheet, you are going to have to
modify the macro that was created. If you didn't use one, you're good to go
on right now.
To edit the macro, choose Tools | Macro | Macros and pick the one you
recorded and click the [Edit] button.
First look for a line that looks much like this:
ActiveSheet.Unprotect
and go to the end of it, hit a space and add this to the line
Password:="mypassword"
so it should end up looking like
ActiveSheet.Unprotect Password:="mypassword"
next look for the line that protects the sheet again:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
go to the end of that line and add this
, Password:="mypassword"
It should look like this when you finish (this is all actually on one line)
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True,
Password:="mypassword"
substituting your actual password for mypassword in both places.
Close the VB Editor. You're done there.
Back on your sheet put a text box from the Drawing tools and enter some text
in it like "Click Here to Sort". You could make the first row kind of tall
and stick the box in that area for convenience. To get to the Drawing tools,
choose View | Toolbars | Drawing
it will appear down at the bottom of the Excel window.
Right-click on the text box (you can now drag it around to position it
neatly on the sheet) and choose Assign Macro and point it to the macro you
recorded earlier. Voila! The user can click that to automatically unprotect
the sheet, sort the data, and put the sheet back into protected mode without
having to ever know the actual password.
You can also right-click on that text box and choose Format and dress it up
by giving it some color.
This should offer adequate protection from casual, accidental alteration of
your worksheet. An industrious individual might open up the VB Editor and
see your code and use the password there to unprotect and modify the sheet.
But you can protect your VB Code also, with yet a different password
(definitely write it down so you don't forget it because if you lose it, you
can't change the code). To do this, follow these steps:
Press [Alt]+[F11] to get back into the VB Editor.
Choose Tools | VBAProject Properties
and click on the [Protection] tab
Check the box labeled "Lock Project for Viewing" and give it a password
(this password is separate from the one used to lock the worksheet) and close
the VB Editor again. Now there's little chance that someone will "pick the
lock", at least not accidentally.
Cathy said:
I've created a worksheet with one column of data (hospital room numbers) that
always remain constant. I have 5 columns where data (numbers) will be
entered daily. I have a calculation column where the numbers in the previous
5 columns are totaled (hidden column). I then have a last column that
returns a specific word based on the value in the previous calculation
column.
I need the first column with room numbers, the hidden column with the
calculation, and the last column with the word value to be locked so they
cannot be manipulated. I can do all those things very easily.
The problem is that a user will have to enter data in the middle 5 columns,
and then sort the spreadsheet, and Excel will not allow a sort using the
locked fields.
Any suggestions?
