VBA code to disable copy/fill function on single workbook only

[greyscale]

Hi,

I have an Excel 2010 workbook that is a central register updated by a team of people. I am finding that they are filtering the data and then using the copy/fill function which is overwriting data and knocking out the conditional formatting. I would like to prevent them being able to use copy/fill/drag but only on that particular workbook and for anyone who opens it. When I use

Please Login or Register  to view this content.

I am finding that all open workbooks have the function disabled. I am very new to VBA. Can anyone give me the code just to limit it to the current workbook, and even the current worksheet (which is called Active)

Thanks in advance

[Richard Buttrey]

Hi,

That command is an Excel Application command and will apply to any instance of a workbook open in that Application.

Have you considered unlocking only those cells that you want users to be able to edit and then making sheet protection active?

[greyscale]

Hi Richard,

What I am finding is that it actually just unchecks my options > advanced setting so that any excel workbook I open in future is also disabled. I don't believe sheet protection can assist me as I need the users to be able to enter comments into all cells in the document. I just need them to manully enter or use the copy/paste into individual cells instead of drag down when they want to repeat an entry. Looks like it can't be done?

[Richard Buttrey]

Hi,

I don't follow this. If you have cells that you say users are overwriting with a drag and drop, then just make sure that those cells which mustn't be overwritten remain locked, which they will be immediately you switch on sheet protection. Unlock all other cells that you want users to be able to copy, edit or whatever. Or am I missing something?

[greyscale]

Hi Richard.

Sorry. I'll try to make this more clear. This is a change register. Each user is responsible for a number of items in the file. (I am simplifying this and there are actually many more columns) but for example, Column A is for date of update, column B is for the Action Owner, Column C is for the update comments etc. What is happening is that the users filter for the items on which they are owners. Then, instead of entering their updates into individual cells they are dragging down on the 'date of update' or comments, if the comments are the same. Everytime I go into the document, I find that the conditional formatting is suddenly all over the place and they have overwritten the comments for the action owner below them. This is what I mean by overwriting. They are not overwriting cells for fixed data, they are overwriting cells that need to be updated by someone else. I don't know how they're doing this because when I test drag/fill with filtering I don't have this problem. I'm just trying to force them to enter individually into each cell to protect the conditional formatting and other users comments. Does that make sense?

[Richard Buttrey]

Hi,

Can you upload an example workbook (anonymised as necessary) since I can't replicate this behaviour. For instance with hidden rows if I drag and fill values from a row above the hidden rows, past the hidden rows to rows below then the hidden rows remain with their original values. If we can see the actual workbook, or at least the relevant sheet then maybe something will click.

Regards

[greyscale]

Hi Richard. I was having trouble reproducing the problem too. i think what they are doing is hiding rows and columns instead of using the filter. If you hide the columns, and then use either drag/fill or copy/'paste to range', all cells update, including hidden cells. The sheets are extremely complex with calculations all through them so I could not reproduce an example workbook. I have attached just a sample of one of the sheets but it is not really representative of the real one.

I have tried VBA code to disable COPY/PASTE but I think this is too risky as it turns off the feature for all open workbooks until such time as they close excel down. I foresee confusion with this. A couple of the users are very non technical. They struggle with even the most basic excel features despite constant training. Now that I think I have narrowed down the issue to hiding of rows, my best bet would be to protect the sheet from the ability to hide rows or columns. This would force them to use the filter.

Given that all cells need to remain unlocked to allow update of cell info, do you know how I can prevent them hiding rows or columns using protect sheet? I've tried googling but can't find anything.

[greyscale]

I think I have found the answer. Make all cells unlocked. Add filters. Size columns and rows and add wrap text (as user will not be able to resize once protected). Then protect sheet with everything ticked except format rows and columns.

I'm still interested in knowing if it is possible to stop drag/fill and copy/paste on a single worksheet only without affecting other open workbooks but the protect sheet might get me by for now. thanks for looking into it.

[Richard Buttrey]

Hi,

Why not set any Autofilters you want to allow and then protect the sheet with the 'Use Auto Filter' ticked? This will stop users being able to hide rows and columns.

[greyscale]

Hi Richard. Yes. That is what I have done. Thanks.