how can I prevent my users from Cutting, Deleting, Inserting

lmm2001

New Member
Joined
Sep 9, 2002
Messages
4
Dear,

I am making an Excel input template. I do not want my users to cut and paste, or delete/insert any rows, which will mess up my reference. But I cannot lock the sheet because they should be able to copy and paste in the input area. What shall I do? Thanks a lot for your advice!

Sincerely,

lmm2001
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Hi imm2001,

As just an idea, how about disable menus *cut,paste,insert and delete* ? :grin:
And you will do well to disable short cut keys too.
 
Upvote 0
Hi, Colo,

Thanks a lot for your message and it is enlightening!

Could you tell me how to disable the menus?

I got rid of the several buttons and deleted the choices from drop down menu, but when my user right click his mouse, he still can use "Cut". or if they know how to add back buttons, they can bring the buttons back.

Thanks again for your help!!!

lmm2001
 
Upvote 0
I was testing this and in the past few seconds came up with this much so far:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

'(Insert code here)

End Sub


I know this is a start.
 
Upvote 0
I know this will come as a bit rude, but since I have seen this scenario before, here goes:

You haven't even tried locking/unlocking cells, protecting/unprotecting sheets to see what it does, HAVE YOU??????

If you got off your butt, and unlocked the cells you wish the users to cut/paste into, while protecting the sheet, you will find that:

Users can cut and paste.
Users cannot delete rows, insert rows, deleted columns, insert columns, etc.

What makes you think any of us come by this information in our sleep, or through divine inspiration? WE BUST OUR BUTT PLAYING WITH THE DAMNED SHEET, UNTIL WE GET WHAT WE NEED.
 
Upvote 0
go to tools, protection, you have an option to prtect sheet or workbook put in your password so no one can do any thing with this sheet

hope it helps (:
 
Upvote 0
Highlight your inputs cells, right click, choose format cells/protection, uncheck locked. Then do the tools/protect sheet and your users should only be able to enter data in the unprotected input cells. Hope this helps. And, let all please be kind and get along on this board. :cool:
 
Upvote 0
Thanks, everyone.

Here is the story. I used the template last year. I unlocked the input cells, protected sheet/workbook with password and give specific instructions to my users "please do not CUT and Paste". Since the workbook was locked, they could not insert/delete rows/columns or any locked cells but they still ignored my words and CUT/Paste within the Input area which was unlocked or unprotected.

When they cut Cell1 and paste into Cell2, the reference Cell in another worksheet lost its reference. It gave me a lot of headache. So what I am trying to do this year is to prevent my users from cutting and pasting. They cannot find the button, they cannot use short-cut keys, they have to correct mistakes by manually key-in or copy and paste...

Now I am working on some codes to remove Cut from rightclick button list as suggested above. Not quite there yet.

Thanks again for all your help. :smile:

lmm2001
 
Upvote 0
IT's not possible to cut a protected cell.....if there is a formula in there, then lock the cell.


Copy a protected/locked cell, certainly. Cut a protected/locked cell, No Way. Check your code, don't reveal the password, make it a longer password. If you use VBA, then protect the project (from the VBA editor).

When you follow up with your users, do their memories of what happened agree with your suspicions? If they insist they didn't do what you accuse them of, then dig deeper.

How many formulas? You might want to consider removing the troublesome formulas from the sheet and have Worksheet_Change do the calculation.

My current project has tons of validation, cross-cell checking, etc., the only formula I use is in three cells: Today().

Also, Worksheet_SelectionChange may be of use, though I haven't used it. You could use the Intersect method and MsgBox users when they are where they shouldn't be.

But actually, now that I think of it, the best way to keep users out of locked cells is this:

sheet1.EnableSelection=xlUnlockedCells

If there is a formula in the cell, they have no business there, correct?

They can't select the cell.

And possibly:
Worksheets(1).ScrollArea = "a1:f10"

Which keeps them in the specific boundaries of the designated area. Also keeps them from selecting the entire sheet and pasting into another workbook; disabling the protection.

These commands would be placed in workbook_Open.

Yes, it's true, if macros are disabled, the safeguards won't be there. Is your problem malicious users, or unskilled users?

You can't stop a malicious user, just the unskilled ones.



You also need to make sure you and you only have Write privs on the folder where the file is stored. If you use SneakerNet, then by all means burn it onto a CD-ROM.

This is one possible scenario to corrupt a workbook:

Copy the sheet, paste into new workbook.
Save the workbook as the original workbook's name, back into the original location.

No More Protection.

If you aren't the only person capable of writing to that folder, then perhaps the above scenario is what happened?

Of course, if the PageSetup is intact, chances are the corrupted workbook is the original workbook.
This message was edited by stevebausch on 2002-09-11 16:56
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,073
Members
453,593
Latest member
Mubashar Ali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top