Protect all cells but allow data validation dropdown change

[Steven]

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]

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

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

 

[Steven]

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]

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

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

 

[Steven]

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]

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

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

 

[Steven]

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]

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

Hi Steven,

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

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

Ken Johnson

 

[Ken Johnson]

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

Hi Steven,

Just thought you might be interested in some code posted by Jim Rech
that I saw recently.
It is a different approach to stopping users from pasting.
It works by disabling the Menu commands and keyboard shortcuts.
Jim's code originally only disabled Cut, Copy and Cell Drag and Drop,
which suited the question he answered, and I have extended it to also
disable Paste and Paste Special... to maybe suit your needs.
It works between different Excel sessions so that something copied in
a different session cannot be pasted in the protected session.
The three Subs are Private so that they can only be run from within
the VBA Editor.
To turn Cut, Copy, Cell Drag and Drop, Paste and Paste Special... off
run CutsOff().
Run CutsOn() to turn them all back on.

Private Sub CutsOff()
AllowCuts False
End Sub

Private Sub CutsOn()
AllowCuts True
End Sub

Private Sub AllowCuts(bEnable As Boolean)
Dim oCtls As CommandBarControls, oCtl As CommandBarControl
Set oCtls = CommandBars.FindControls(ID:=21) 'Cut
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
Set oCtls = CommandBars.FindControls(ID:=19) 'Copy
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
Set oCtls = CommandBars.FindControls(ID:=6002) 'Paste button
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
Set oCtls = CommandBars.FindControls(ID:=22) 'Paste in Edit menu
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
Set oCtls = CommandBars.FindControls(ID:=755) 'Paste Special...
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
''Disable Tools, Options so D&D cannot be restored
Set oCtls = CommandBars.FindControls(ID:=522)
If Not oCtls Is Nothing Then
For Each oCtl In oCtls
oCtl.Enabled = bEnable
Next
End If
With Application
.CellDragAndDrop = bEnable
If bEnable Then
.OnKey "^x"
.OnKey "+{Del}"
.OnKey "^c"
.OnKey "^v"
Else
.OnKey "^x", ""
.OnKey "+{Del}", ""
.OnKey "^c", ""
.OnKey "^v", ""
End If

End With
End Sub

Special thanks to Jim Rech.

Ken Johnson

 

[Steven]

Ken,

I just now saw this. Thank you very much.


Steven

 

[Ken Johnson]

Ken,

I just now saw this.  Thank you very much.

Steven

Hi Steven,

You're welcome, hope it helps.

Ken Johnson