Re-enable right click options, disable cut functions

I need a macro to disable all cut options (ctrl^x, right click cut, cut button in ribbon above), disable drag and drop, but still allow copying and pasting (the cutting affects cell references). I've already locked the spreadsheet, but users need to be able to input information and paste information in from other workbooks. This code must also only apply to THIS WORKBOOK and not affect others. Please help with a macro for this, as well as where to paste and how to properly configure macro security settings. I'm a big excel user, but this is my first time ever with VB and macros so talk to me like I'm dumb! I've pasted links to the two codes I tried, and I think one of them enabled a macro that affected all workbooks and now whatever code I put in won't save upon re-opening. It's there when I navigate to it, but it is ineffective like it's not even there. In macro security, I've enabled all macros and have the "trust VB programming" box checked. So maybe do some damage control before re-programming. I am getting a new computer in less than a month, but I want to be able to try out the code and have it work first, and I don't want to transfer the harmful code to the new computer. Thanks a ton!

P.S. This first code allows copying and cutting, but then just disables pasting, which is not what I want. The second one works great, but disables the right click menu altogether, which is no good either. And neither allow for pasting from the outside. And this is for moderate excel users, they won't try to erase the macro. I just need to keep them from making mistakes with cutting.

http://www.ozgrid.com/VBA/disable-cut-copy.htm

http://www.mrexcel.com/archive2/75500/87639.htm

Are you usin Excel 2007?

This problem is solved, but I can't see any drop down box to select that option like the instructions say.

To enable the right click menu again, I used this code in the "immediate section."

Please Login or Register  to view this content.

And to get the code to take effect, I had to unlock the "workbook" structure, and then it all worked. The only downside is that I can't hide a worksheet and keep people from unhiding it.

jman .. have you looked at:

Please Login or Register  to view this content.

You might have to unhide it to use/refer to it in the code, but you can turn off screen updating while doing this. This property can only be set in code, so users won't be able to unhide it from the GUI.

Hope that helps. MM.

If you want to post your file I'll take a look ..

Attached is my workbook.

When I look at the object, it says Sheet7(CONTROL). CONTROL is what I named it's tab, so I'm not sure what to put in the parentheses. I tried using quotation marks, and couldn't get any good results.

MatrixMan (or anyone else), when you're in there, do you think you could check the programming in sheet 3 for my other post? The Link to it is below. It's very important and I'm trying to get it finalized by the end of the day tomorrow. The code that the other person gave me is in the sheet, but if you read my post it needs to be fixed a little bit. I left the sheet unprotected, but it will need to be protected in the end with a password. You would be a HUGE HELP! I'm pretty much a beginner at VBA, but I know excel well for formulas etc. And since it's a VBA question I'd figure you guys would know what you are doing! Thanks a lot!

http://www.excelforum.com/excel-work...-of-cells.html

LOL ... I see ... it took me a while to work out what you were on about, but I see what you mean Answer is: your sheet is referenced either by:

Please Login or Register  to view this content.

Or by

Please Login or Register  to view this content.

You've changed the tab (which is the named reference), so

Please Login or Register  to view this content.

... doesn't exist anymore and is invalid. What you call the Object in the VBA properties window is just showing the sheet index (the fact that it's the 7th sheet, not that it's called "Sheet7") and the name together.

It's a bit confusing I guess, but you get used to it Hope that makes sense ... I've also looked at the 2nd question re the other post, but I see that someone has already responded there.

I see what's happening. When I close the document, there is some sort of error that happens with this line of the code in "ThisWorkbook." If you re-open my file, you can see it. This is the line it highlights, so there must be some sort of argument conflict, and that's what's keeping it from hiding the sheet. And it doesn't even take effect right away like my other code has, it won't even work before I close the docuement.Did you get it to work?

Maybe I'm pasting it in the wrong place, I've been pasting it in the sheet 7 VB window.

Please Login or Register  to view this content.

Hi jman .. the Workbook_BeforeClose module is a system event module; it is supposed to run under the workbook object ... I don't know why you'd run it from the worksheet object? Also, remember that if you are in code that is in a worksheet object, you don't have to specify a sheet unless it's not the one you're in ... Similarly, if you're in the workbook object, you don't have to specify "ThisWorkbook" .. you're already in the object, so it's implied. Make sense?

That Workbook code is in the workbook object. When I run the veryhidden code in the worksheet object, and then close the workbook, it highlights the aforementioned BeforeClose line in the workbook object AND says invalid outside procedure and highlights "xlVeryHidden" in your code in blue.

So what do I change in the code you gave me if I'm pasting it in the object of the sheet that I'm hiding? (I want to hide sheet 7 aka "CONTROL" and that's the worksheet object I pasted it in)

I did upload the workbook a couple of posts ago, maybe you could just paste your code in there and see what happens. The other code is to disable the cut function so it needs to stay somehow. Thanks!

I must be looking at a different file because I can't see xlveryhidden anywhere and certainly not in the CONTROL worksheet ... can you re-upload and I'll have a look? My other comment was in response to your statement that you pasted it in the control sheet and then had code underneath refering to a workbook event ... just me getting confused perhaps.

so just to confirm ... did you put:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

... into Sheet7 ?? What triggers it? Or is it just something you want to stay set all the time? (in which case, it's already set now, and your other code runs ..)

I tried all 3 and got the same error. I've attached my workbook so you can paste your code in worksheet7 and see exactly what happens. The code doesn't take effect at all, and when you go to close the workbook, you get 2 errors. This line gets highlighted yellow in thisworkbook object code:

Please Login or Register  to view this content.

And this part of your code gets highlighted blue and says "invalid outside procedure"

Please Login or Register  to view this content.

So if you could open my workbook and paste your code in there (it's not in there now) you can see exactly what happens. I need the other code in there, but just want your new code to be able to hide the "CONTROL" worksheet (sheet7) from users at all times so they can't right click and unhide it. Thanks!

And like I said before, protecting the workbook structure DOES accomplish this, but I can't have the workbook protected otherwise my other code doesn't run (the code to disable cut and drag & drop).

Hi jman ... well, I just can't get the error to replicate I've put the following code in your Workbook_Open event (not sure why you've put it in the close event, but that shouldn't make any difference):

Please Login or Register  to view this content.

... and it runs with no problem. Perhaps it's something peculiar to XL2007, which I don't have on this machine so can't really comment on ... anyone?

It works now, I was pasting it in the wrong place. Since I know nothing about VBA, that was the problem! I was pasting it in worksheet 7 object area, and not in "myworkbook" object.

BUT.....When i want to work on the control sheet, I delete the code, resave, and reopen. But I still can't unhide it. I even disabled macros and every other security option, and it still won't unhide. Is there a code I can put in the workbook_close event to reverse the action, or maybe tweak the code you wrote to not hide it so permanently? Or at least have a code that I can paste in there to unhide it so I can work on it? I need to be able to work with the CONTROL sheet still. Thanks!

Actually, I don't have XL-07 ... but you can download file converters from the MS site Glad it worked out for you ... Cheers, MM.