Excel - Protect all cells but allow data validation dropdown change

Asked By Steve on 13-Jun-08 05:59 PM
I have a file that I want to protect all cells.  But at the same time there
are certain cells that have a Data Validation Dropdown where I want the user
to be able to select an item off the dropdown list and update the cell.  I
want to protect everything because I do not want anyone to be able to "Paste"
anything in the file or make any changes in the file except for making
selections from the Data Validation Dropdown and no other way.

For example:  The cells that have dropdowns must be in protected mode when
user enters the cell because I do not want the user to be able to take a cell
from somewhere else and copy / paste over the Data Validation Cell I have
setup.  Therefore I need the protection as mentioned above.

How can I accomplish this?
Thank you for your help.

Steven




Ken Johnson replied on 14-Jun-08 03:47 PM
Hi Steven,

AFAIK you need to use VBA to prevent loss of validation caused by
pasting.
Before a user can paste over the validated cell they have to select
it.
The Worksheet_SelectionChange event with Application.CutCopyMode =
True (or Application.CutCopyMode = False) can then stop the user
pasting.
For example, say the cell with the validation dropdown is Sheet1!A1,
then Sheet1's code module could be...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Application.CutCopyMode = True
End If
End Sub

This on its own will only work when the user copies from the same
sheet as the validated cell, that which he/she intends pasting.
The user could select the validated cell (A1), switch to a different
sheet, copy, switch back to the sheet with the validated cell already
selected, then paste and destroy the validation. The
Worksheet_SelectionChange would not be triggered to prevent the
pasting.
So, the Worksheet_Activate event also has to be used to change the
selection if it includes the validated cell...

Private Sub Worksheet_Activate()
If Not Intersect(Range("A1"), Selection) Is Nothing Then
'Force reselection of A1 to trigger SelectionChange sub
Range("B1").Select 'any cell other than the validated one
End If
End Sub

Similarly, to cover the pasting of stuff copied from another workbook,
the Workbook_Activate event in the ThisWorkbook code module has also
to be used...

Private Sub Workbook_Activate()
If ActiveSheet.Name = "Sheet1" Then
If Not Intersect(Range("A1"), Selection) Is Nothing Then
'Force reselection of A1 to trigger SelectionChange sub
Range("B1").Select 'any cell other than the validated one
End If
End If
End Sub

Ken Johnson
Steve replied on 14-Jun-08 03:00 PM
Ken,

Thank you for the help.  One more issue.  What if the person opens another
session of excel and copies out of that session of excel and switches back
and pastes.  How do you catch that?

Thank you,

Steven
Ken Johnson replied on 16-Jun-08 11:51 PM
Hi Steven,

Maybe I misunderstand the scenario you described, but doesn't the user
still have to activate the workbook with the validated cell before he/
she can paste, and this activation changes the selection so that he/
she then has to reselect the validated cell, triggering the
SelectionChange sub that prevents the paste.

Have you been able to override the validation the way you described?

Of course one other way the user can override the validation is to NOT
Enable Macros when opening the workbook. Then you would have to set up
your workbook so that when it is opened without Enabled Macros all
sheets bar one have their Visible property set at xlVeryHidden. The
remaining visible sheet can carry a message stating that the macros
are a vital part of the workbook and that the user should Close then
re-Open and Enable Macros.

Ken Johnson
Steve replied on 15-Jun-08 12:28 PM
Ken,

Thank you for responding again.  My experience in testing this is where a
user opens a second session of excel.  That is not a new workbook in the same
session but actually starts another application session (now we have two
sessions of excel running).  I am going to say something here that you
already know but I just want to be clear. I have started Session One of excel
and open files File001.xls ; File002.xls ; File003.xls and then open another
session "Session Two" of excel and open files File004.xls ; File005.xls ;
File006.xls .  Then if I am hitting CTRL+TAB and I am in the Session Two then
I will cycle files 4, 5 and 6 ; but not 1, 2 and 3 because they are in a
completely different session.  Now if I am in Session One File002.xls and
Copy and go to File001.xls to paste.. yes ... it will disable the paste
because the Workkbook_Activate subroutine ran.  But if I am in File001.xls
and then click on the taskbar to Session Two to File004.xls and Copy and
click on the taskbar back to Session One and do not move the cursor, because
I was being sneaky and already had the cursor where I wanted it, then I am
able to paste because none of the events were triggered in this case.

Am I correct?  Am I missing something here or is there still another catch
to be made.

Thank you very much for your help.

Steven
Ken Johnson replied on 16-Jun-08 11:52 PM
Hi Steven,

It looks to me like you have found a hole that can't be filled!
Switching between different sessions does not trigger any of the event
procedures.

Perhaps a completely different approach is called for.
After the pasting has been done the Worksheet_Change event is
triggered so maybe you could use VBA to repair the damage after it has
occurred.

What are the details of your validation?

I must admit to being a little confused. I always thought that pasting
over a validated cell destroyed the validation. After a little
experimenting that seems not to be the case. When I copied a cell with
one type of validation then pasted into a different cell with a
completely different type of validation, its (the cell being pasted
into) validation did not change, but the unallowed pasted value was
allowed.

So, maybe all that the VBA code has to do is back up the validation.

What are your thought?

Ken Johnson
Steve replied on 16-Jun-08 01:07 PM
Ken,

I have worked with the OnChange but it seems to create additional issues ;
at least in the file that I am working with.

The easiest solution appears for me to protect all cells and set the
password having all cells protected.  Then on the OnSelection ;  if the cell
meets a certain criteria (such as has borders outlined and is a certain
interior color then I run a message box asking the user if they want to
update the cell).  If they say yes then I unprotect .... make the change ...
and then protect the sheet.  If the cell requires a response that cannot be
answered yes / no then I open a form and the user inputs something ; I test
if ok ...  I unprotect ..   make the change ... and protect.

There is a lot of testing in this file and on the Auto_Open I find who the
user is that opened the file and save it to a variable, so I am always
testing that to see if they have rights to make a change.

One other thing I am working with is getting a cell value w/o opening a
file.  I use this to test an additional passcode for a file.  For example: In
my code on the Auto_Open I may have varPasscode = "ADND2314213".  When the
file opens it goes to a secret file where it reads a specific cell in the
secret file. Then it compares this back to the a code I have hardcoded in the
Auto_Open.  This seems to work well, in that since this is such a controlled
file that if I make a change in the structure of the file I will reset the
passcode.  The secret file has random codes (ie numbers) in Sheet3 from A1 to
E2000 (ie 10,000 passcodes), but the trick is I only change the cell that
holds the actual code that I use.  The A1 to E2000 is just to confuse anyone
who wants to be sneaky.  What this does is if I have changed the passcode but
a user decides they are going to overwrite the file with a previous days file
(which may have a different passcode then the cell in the secret file may
have been changed and the system will notify the next user going into the
system there is an "Administrative Error" and it will close the file w/o
saving.  Then I just have to go find who overwrote the file and severly
repremand them.

Thank you for all your help.  Sometimes you spend an inordinate amount of
time on something and it just keeps getting more involved.  But I think the
method I discuss here works ok.

I appreciate all your help.  Take care.

Steven
Ken Johnson replied on 16-Jun-08 11:54 PM
Hi Steven,

That's amazing stuff, you have been very inventive.

Also, thanks for revealing the weakness (different Excel sessions) in
my original method.

Ken Johnson
Steve replied on 07-Sep-08 06:16 PM
Ken,

I just now saw this.  Thank you very much.


Steven
Ken Johnson replied on 10-Sep-08 09:10 PM
Hi Steven,

You're welcome, hope it helps.

Ken Johnson